Reference another sheet using sheet name
I'm almost certain that there is no solution to this situation, however, here goes. I have a workspace with approximately 35 project sheets. Each project has approximately 50 tasks and each has a schedule status in a "Schedule" column. I want to be able to count these tasks based on their stats and report on the entire portfolio on a dashboard.
I could create reports based on the schedule status, but I can't calculate anything on a report.
I could create a schedule summary row on each sheet and input countifs formulas in that row, but there is no way to compile all projects into 1.
I could create a sheet with a row for each project and a column for each (of 4) stats types then manually create 70 named references (2 for each countifs formula) and manually update this each time a project is added or removed. Then sum the columns and reference that summary column to the dashboard.
- This seems to be the only way that will work, but is extremely cumbersome. I'm not sure if the sheet would function well with over 70 named references.
- To make this less cumbersome I was wondering if it was possible to use the sheet and column name in a formula to reference another sheet. See attachment.
Any solutions out there?
Thanks in advance.
Comments
-
Hi Jacob,
Not sure if this is one of your options.
You could create a summary row or section on each sheet, and then cell link them to a master sheet and do more calculations on the information there.
Would that work?
Have a fantastic day!
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
That would be a solution combining a couple options I've mentioned, however, I would still have to manually create Named references for each sheet and each new project. I'm hoping to find some sort of automation.
-
You wouldn't need to do any named references because you would use cell links (not cross sheet links) instead.
Would that work?
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I would have to manually create the cell links instead of manually creating named references correct?
-
That's correct, but I think it will probably be the easiest solution.
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I agree with Andree. In this particular case, it seems like using cell linking would be the easiest way to go to pull all of the required data onto one sheet and then run your totals from there.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives