Help with formulas to summarize data from multiple sheets

carlycrisp

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!

Leibel S

@carlycrisp

1. Prefill your summary sheet with a bunch of rows (however many phases you think there will be)
2. Create column called Row ID  (system column Auto-Number)
3. Create Column Called ROW# this should have a formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
4. 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)