SIGHT Metrics

Ally

I am part of a PMO and we track all projects on one sheet.  The PMO covers multiple lines of business within our organization, thus I have a Sight set up per line of business.  In each Sight, I would like to be able to show the number of projects in each RYG status.  What is your recommended approach for doing this?  I've been playing around with a number of different possibilities, but all seem to be convulated, confusing and make the Sheets messy. Thank you.

L_123

Do the math on a hidden column inside the project sheet itself. Simply add a column, and use the countif function on the entire columns applicable to get the information you want in the column. Use a cell grab in the sight to grab the specific information you want. 

Green

=Countif(column:column,"Green")

Yellow

=countif(Column:Column,"Yellow")

Red

=Countif(Column:Column,"Red")

 

If you want you can even make it a single cell by concatenation then just report the one cell to the sight.

 

=Countif(column:column,"Green") + " " Countif(column:column,"Yellow") + " "+ Countif(column:column,"Red")

put this in the first cell then use a creatively spaced title for the column, and that is another option.

When you are done right click on the column header and select hide. You'll always be able to reference it or grab the information for reports and sights, but you don't have to see it in your sheet.

If there are projects being added to the top of the sheet just do the same process but put the information into a report then into the sight to retrieve it. That way you can filter out the blanks.

 

L_123

after rereading this i'm not sure I understood. Can you post a screenshot so we can see what you are talking about with the format and the grab? Also do you need to be able to sort/filter the rows? Where are you adding new rows?