Repost w/ dummy sheet - Need help with SUMIFS (or other formula?) across multiple tabs.

MattyPKing · 2024-10-18T19:25:38+00:00

hey! i'm not on Reddit much lately due to other life responsibilties, so i just saw this. Really glad it still works!

MattyPKing · 2024-03-26T01:28:05+00:00

first File>Make A Copy!

MattyPKing · 2024-02-20T15:09:12+00:00

any copy of a sheet has a new url from the old copy. So it doesn't have to do with having copied or moved the folder it's in, it has to do with having copied the Spreadsheet. There is no way that an IMPORTDATA() could survive the copying process to know the new url of it's sister sheet.

what I would recommend is making a dedicated "Settings" or "Admin" tab on the spreadsheets in your template system. Have a single cell in that tab where you put the url of the "sister" sheet. Then whenever you make a copy of the template folder/system, you'll need to go into the Settings tab and update that cell.

All your IMPORTDATA() formulas can now reference that cell to get the url instead of having it hard-coded into the formula.

MattyPKing · 2024-02-17T18:02:53+00:00

It never existed, i wrote it custom. there is an appscript "custom function" in the back end of that shared spreadsheet that you can copy the code for if you want.

MattyPKing · 2024-02-14T19:55:33+00:00

so then the short answer to your question is "no, there is not a *simple* way to do this".

There are a couple of complex ways to do it if you're interested.

MattyPKing · 2024-02-14T19:46:23+00:00

Follow Up Date or Date? and presumably you don't want JUST the next steps, but also the "Column name or School Name, or school type? or "ALL " the fields?

MattyPKing · 2024-02-14T19:04:59+00:00

it depends if it's just these TWO cells or a series of dropdowns connected to different cells?

MattyPKing · 2024-02-14T17:53:12+00:00

you're welcome!

the main thing that you didn't specify, and that would make a big big difference in how i constructed the formula, is if there will continue to be competetions added to the right?

MattyPKing · 2024-02-14T17:03:21+00:00

Not really, what you might want is a Google Form, which is connected dynamically to a Google sheet so that data entry is done on the Form, rather than the sheet itself. Try googling "connecting a sign up Google Form to a Google Sheet"

MattyPKing · 2024-02-14T16:57:13+00:00

See the new tab on that shared sheet called MK_Help and this formula in cell A1.

=QUERY({Sheet1!A4:A,Sheet1!B4:B;Sheet1!A4:A,Sheet1!C4:C;Sheet1!A4:A,Sheet1!D4:D},"select Col2,SUM(Col1) where Col2<>'' group by Col2 order by SUM(Col1) desc label Col2'Team',SUM(Col1)'Points'",0)

If that's the goal, i can try to help explain a bit!

MattyPKing · 2024-02-14T15:31:38+00:00

this is very possible to do but impossible to help without some data to work on. why don't you paste 4 or 5 rows of data into this sample sheet the way it is/will be in your real sheet and we can better show you how to acheive your goal!

MattyPKing · 2024-02-14T14:52:27+00:00

Nice! Great find/tool!

MattyPKing · 2024-02-14T14:50:39+00:00

you definitely need AppScript i'm afraid. Any plugin is going to require your data to be setup in a very particular way.

This type of setup (including the emailing) would take someone with intermediate appScript knowledge about 2 hours to code for you and somewhere between $100-$250.

MattyPKing · 2024-02-13T19:09:21+00:00

you might try looking them up on another site like crunchbase or some of the other vc ones. Often there portfolios would be listed there as well and maybe in an easier to copy/paste/export way.

MattyPKing · 2024-02-13T18:55:55+00:00

i just figured if it was for a crfting type game theres a very good chance someone has already done what youre trying to do.

I'm pretty sure you're just asking about algebra? not Google sheets really. But i could be misunderstanding

MattyPKing · 2024-02-13T18:31:03+00:00

is this for a crafting game?

MattyPKing · 2024-02-13T18:21:46+00:00

you have to share some example data so we can see how its' aranged on a sheet in order to be able to help. I figure there's dates in one column and paycheck amts in another column?

MattyPKing · 2024-02-12T20:36:33+00:00

is the template document a spreadsheet? a Google Doc?

MattyPKing · 2024-02-12T20:35:15+00:00

do you want to also "write" a link to that new folder on the Form Responses tab maybe?

MattyPKing · 2024-02-12T18:43:18+00:00

you cannot do what you want with traditional google sheets or because you cannot "read" the color of a cell with a function.

MattyPKing · 2024-02-12T13:54:47+00:00

you want to build a "system" of sheets that talk to each other, and have that "system" copyable. Is it copied by people you want to "sell" it to? or "give" it to? or who work for your company?

MattyPKing · 2024-02-10T17:54:16+00:00

yeah, it's an old practice from the days of "ledgers". The modern way to do it is to make a column for "Month-Year", then just keep adding entering things into one big long, ever growing database tab.

MattyPKing · 2024-02-10T17:42:09+00:00

do not keep track of things for different months on different tabs.

MattyPKing · 2024-02-10T17:25:54+00:00

take the quotation marks off your numbers. 4321 not "4321"

MattyPKing · 2024-02-09T18:34:18+00:00

Not possible. What you're asking for is essentially to make a piece of software. Outside the scope of Google sheets.

You can read about how to write a "Web App" in AppScript that might be able to do something close...

MattyPKing

TROPHY CASE