Referencing Data across Many, Many Sheets

SYSPK

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

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