Pull in % Complete into a new sheet (pivot view)

ilene_healy68056

Hi

I am looking to create a ‘pivot’ view of my sheet that will show the 6 stage gates going across horizontally, so we can see going down the sheet how we are doing across all sites for system, users, WoW, etc.  I have created a new sheet for this view and plan to reference in the 3 project sheets.

It was recommended that I try an INDEX(COLLECT formula, to bring back the % Complete for the Stage Gates by Site and Capability.  But this is bringing back the first instance of the Capability it finds.  For example, the % Complete is the same for “CTP” for all sites.  It is not looking past the first occurrence.  

Here is my formula including off-sheet references to my project sheet for the Percent Complete, the Stage Gate + Capability.  I think Site needs to be in there somewhere:

INDEX(COLLECT({Percentage}, {Stage Gate}, "System", {Capability}, [Site - Capability - Stage Gates]@row), 1)

Screen shots include samples from 2 sites for the detailed project view and then the same 2 sites for the pivot view that I am trying to get to.  You can see the % being repeated incorrectly on the purple pivot.  I did add a key column that concatenates the site+capability+stage gate….if that helps get me there.

I know this is a lot, but I really appreciate any thoughts for the best way to tackle this.  My management is keen on having this pivoted view. 

Thanks for any advice you can offer

Ilene

Paul Newcome

You could try using a JOIN/COLLECT with a series of FIND functions to hit off of your concat column based on the data you used to build your tables with one additional row (totally optional, but makes dragfill possible). For this example, I am going to make it the top row (row 1). In this row, you will simply repeat your column names so they can actually be referenced in a formula (instead of having to manually change the reference for each formula). 

.

So if row 1 is the helper row with the column headers repeated. Row 2 is the parent row for West Point. This formula would then go in System3.

 

=JOIN(COLLECT({Percentages}, {Concat Column}, AND(FIND(PARENT([Site - Capability - Stage Gates]@row), @cell) > 0, FIND([Site - Capability - Stage Gates]@row, @cell) > 0, FIND(System$1, @cell) > 0)))

.

This will search your Concat column and pull the data from the Percentages column where the Parent data, the row data and the column data are all found. Because you are pulling all of this data in the concat column, you have establish what basically amounts to unique row identifiers that we can leverage.

.

If you did not want to use the helper row, you would simply replace System$1 with "Whatever Text" you want.

 

Please note: The $ in the System$1 cell reference is intentional. This will allow you to dragfill down while still referencing that row, but dragfilling to the right will update the column reference (and thus the text that the formula is looking for).