Is a cell value driven cross sheet formula possible?
Our projects have a subfolder with any number of sheets that all have the same data layout.
We need a summary sheet that fetches a status value from each child sheet, but i can't figure out how to do it in a way that wouldn't take 90 hours of tedious work - that would need to be repeated forever and ever
In excel i'd use INDIRECT and be done with this. A column called "child sheet name" and a lookup column using INDIRECT to construct the cell references to the other sheets based on the adjacent sheet name string.
It would be semi manual - but building a list of sheet names isn't a problem - they are room names from the job site, which come from a table that already exists.
Is there a way to construct such dynamic cross sheet references?
Many thanks!
(PS: I have spent many hours searching. Web results are bonkers when using keywords "smartsheet indirect"; a mix of other people with my same problem getting no help, and references to premium S add-ons that have no pricing, no explanations of what they do, and no conclusive evidence that they solve the problem. I'm hoping for better luck with a fresh question! :)
Answers
-
I've yet to be able to find a solution and have EITHER resulted to the tedious aspect when necessary or tried to invert my data positions (and had the child sheets pull from the parent sheet - where it can just be built into the generation of the child sheets) when that is viable.
I'm definitely keeping an eye on this question though to see if someone else has an answer.
-
This sounds like something Control Center was specifically designed to do. Roll out a templated folder set over and over again, and automatically create cell links to data from each individual project's sheet(s) into a Summary Sheet.
.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!