Referencing Data across Many, Many Sheets

SYSPK
SYSPK ✭✭✭✭✭✭
edited 12/09/19 in Smartsheet Basics

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

Tags:

Comments

  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    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.

    thinkspi.com

  • Hello,

    I would create a report combing all the sheets. ;)

  • SYSPK
    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å
    Andrée Starå ✭✭✭✭✭✭
    edited 01/08/19

    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, [email protected])

    Best,

    Andrée

     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • SYSPK
    SYSPK ✭✭✭✭✭✭

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Let me know if you have any questions.

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.