Referencing Data across Many, Many Sheets

What is the best way to do a SUMIF for data that's spread across ~50 sheets? This cannot be done with reports.

My data involves how many hours our clients work in each state. There is overlap because a client may work in more than one state. Each of our 50 state sheets shows which clients work there and how many hours they work in that specific state.

I would like to get a master list of all client names and total hours they work across all states.

I can see SUMIFS being a nightmare having to create 50 references in one formula.

Being able to do a SUMIFS off of a report that pulls all the state sheets into 1 would be the magic answer but I know that feature is unavailable.

Does anyone have an idea of how to work around this?

Thank you

Find more posts tagged with

Cell linking

Comments

Andrée Starå

Hi,

I would either collect everything on the different sheets and then cell link all that information to a master sheet or use cross sheet formulas to collect everything on the master sheet.

The best choice depends on your specific setup and preference.

Would that work?

Happy New Year!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Another option could be to split up your 50 sheets into zones, designate certain states to each zone, and compile the data on the zone level. You would then only have those x amount of zones to pull together in your master sheet. Kind of helps spread out those 50 x-sheet references a bit.

Silvia C Rodriguez

Hello,

I would create a report combing all the sheets.

SYSPK

Thank you all.

I edited my question a bit so it reflects more of what I'm looking for.

If I go the cell linking route, how would that look to link 50 sheets onto 1? Would I need to devote empty rows to accommodate growth in the individual sheets?

Andrée Starå

Happy to help!

You don't need to devote empty rows because what's collected is the sum of everything on that sheet.

You would need to sum all the values you need per sheet and then cell link that to the master sheet that will be used to collect everything, sum it together and to use in the report.

Example. Everything is collected on one row in sheet one, and that row would then be cell linked to the master sheet.

Would that work?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Best,

Andrée

SYSPK

Ahh I see. I will try that first- thank you.

Andrée Starå

Happy to help!

Let me know if you have any questions.

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…