Creating a master metric sheet

Jason MacD

I have been hunting through the threads for a few days, and have not been able to find a clear resource explaining how to set up a metric sheet and then combine into a master metric sheet. I had created a "metric sheet" with my metrics as columns (approx 50 of them), it appears from what little I can find I should have created it as a 2 column sheet instead with my metrics down the first column. that is ok and will re create as needed but then how do I link the 20 or more sheets into a master sheet as new ones will be created/destroyed every few weeks. and the copy functions seem to be row based not column based anyway.

previous attempts have ended up with invalid references when I try copying the cells over.

thanks

Can someone point me to a learning centre article or some other reference.

Jason MacD

The report is not a valid option as stated before, you cannot add or average the KPI value, it must be worked out on the individual sums. I spent enough time over the last few days to work out how to create a master metric sheet which was my original request. I had hoped for direction to a step by step explaining the concept of "Group of (Data Sheet --> Metric Sheet) --> Master Metric Sheet, but was unable to find it.

My solutions and I don't know if this is the standard method, was to

1) create my data sheets

2) create a metric sheet with all the summations and other data required for both use in the master metric sheet and the dashboards at the project level

3) duplicate a metric sheet and link the cells of the individual metric sheets into rows or columns (in my case rows) and then use the sheet summary to do the calculations which were more involved than a simple summation. (if I wanted to graph some of this data I suspect I would have to create yet another sheet to sum the columns in my master metric sheet which doesn't quite seem right.) Possibly this sheet should have been a Portfolio Data sheet, and the Master Metric Sheet be the summary sheet of this one.

Genevieve P.

Hi @Jason MacD

What about creating a Report from all of your sheets? As long as the column names are the same, this will bring together all your content into two columns. Here's a free Webinar on creating Reports.

If this hasn't helped, can you share screen captures of your current set-up (but please block out sensitive data)?

Cheers,

Genevieve

Jason MacD

thank you for your response Genevieve

but unfortunately a report won’t help as I need to do further calculations to work out KPI s and other items if I could access the summary fields of a report from another sheet it would be a viable option.

one example is

DI= items delivered last month (due last month or carried over from earlier months)

DUE = items due last month

OD = items not delivered from previous month

ED = items delivered last month not due till a subsequent month

KPI = (DI + ED)/(DUE+ED+OD)

Easy to calculate on a project sheet not so easy on a department level without a lot of linking. And this is only one of several items I want to calculate as a department whole as well as project level.

Genevieve P.

Hi @Jason MacD

Thank you for describing a little further! What about adding each of your key metrics as formulas in Sheet Summary fields per-sheet? Then you could create a Summary type of report (instead of a Row Report), which would bring together your data across all 20 sheets and roll up to totals at the top.

If this is still not a viable option, I'm not sure I understand your current set-up. Can you post some of the formulas that you're currently using, along with screen captures of dummy-data?

Thanks,

Genevieve

Jason MacD

The report is not a valid option as stated before, you cannot add or average the KPI value, it must be worked out on the individual sums. I spent enough time over the last few days to work out how to create a master metric sheet which was my original request. I had hoped for direction to a step by step explaining the concept of "Group of (Data Sheet --> Metric Sheet) --> Master Metric Sheet, but was unable to find it.

My solutions and I don't know if this is the standard method, was to

1) create my data sheets

2) create a metric sheet with all the summations and other data required for both use in the master metric sheet and the dashboards at the project level

3) duplicate a metric sheet and link the cells of the individual metric sheets into rows or columns (in my case rows) and then use the sheet summary to do the calculations which were more involved than a simple summation. (if I wanted to graph some of this data I suspect I would have to create yet another sheet to sum the columns in my master metric sheet which doesn't quite seem right.) Possibly this sheet should have been a Portfolio Data sheet, and the Master Metric Sheet be the summary sheet of this one.

Genevieve P.

Hi @Jason MacD

There isn't necessarily a set standard method as this depends on how your source sheets are set up and what information you're looking to gather from them.

Usually something like Data Sheets, then a secondary rollup Data Sheet or Report looking at those would make sense. It seems like your specific set-up needs an additional master sheet after the initial and secondary data is gathered, which is what I was unable to understand, my apologies.

I would recommend downloading some of the Template Sets in the Solution Center for other examples of formatting and gathering data, and the structure that may be involved to reach the end goal.

I'm glad you were able to find a solution!

Cheers,

Genevieve