Help with formulas to summarize data from multiple sheets
Hello,
I have a Blueprint set up in Control Center that is used to provision new projects. I want to add a sheet to my template set to compare projected direct labor from the schedule we have set up with the actual direct labor we have feeding in through Data Shuttle. The schedule is set up with phases as the top level and then tasks and sub-tasks below that. Phases and tasks vary by project, but there is a column with the parent level identified with phases having a 1 in the parent column. I'm trying to find a way to set up a sheet that automatically pulls in the phase name when the master schedule is set up for each project, but I can't seem to find a formula that will do that. Once the phase name is in the summary sheet, I have columns set up to return projected labor from the schedule sheet and actual labor from a separate data upload sheet. Any thoughts on how I can make this work so I don't have to manually add the phase names to each provisioned project? Thanks!
Answers
-
- Prefill your summary sheet with a bunch of rows (however many phases you think there will be)
- Create column called Row ID (system column Auto-Number)
- Create Column Called ROW# this should have a formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
- You phase column should have a formula roughly as follows: =INDEX(DISTINCT(COLLECT({reference to column with phases},{reference to phase indentifier},@cell=1)),[ROW#]@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!