Building a Master Sheet using Copy Rows

Jen Thompson

Hi all,

I have ~40 Project Info Sheets that are used to build an executive dashboard and populate status report dashboards. Each of the Project Info Sheets are distinct to the project and then roll up to the report(s) to provide the portfolio level view. Active Status is indicated by an 'Active Status' checkbox and can either be attributed to a new row week after week or the same row can be updated. Only problem is that I want to create metrics off of this. The solution I have come up with (because report metrics doesn't exist) is to copy new or changed 'active status' rows from the project info sheets to a 'Master Sheet' that I can then use to calculate metrics from (e.g., project count, how many red, yellow, green, on hold, etc.). When I copy the row, I'd like to make it the new unique row for that 'project'. Instead it is added to the end of the sheet and so I have duplicates. I have thought of including the Sheet ID as one of the columns to have each row as an identifier... it gets me a unique project count, but 1) that is cumbersome to the project managers to have to pull that every time they start a new project (unless there is some other way to get a unique project ID), and 2) I don't know how to get the latest 'active status' rows added to the Master and their corresponding RAG status. Alternatively, there would be a way to remove the previous rows from the 'Master sheet' so as to only have the 'active status' row in the Master because all of the rows have an 'active status' checkbox checked, so there isn't anything differentiating one row from another, except for maybe the last modified date. Ideally this sheet wouldn't be touched/manipulated, it would just be used for aggregating for calculating metrics. I don't want to use cell linking. This really needs to be a hands off and little/no setup sheet when starting a new project.

Thoughts?

Below is an example.

Ben Goldblatt

Hi @Jen Thompson,

If you're having rows copy over to the Master Sheet to get all of the data into one place, you could consider setting up a separate "Metrics" and using cross-sheet COUNTIFS formulas to populate counts for "Active Status" checks, the various "Status" values, and "Overall health" symbols.

In the "Metrics" sheet (the formula destination sheet), you can list out all of the project names in the Primary column, then set up columns for each of the various counts you need to populate, or maybe separate columns for "Active Status", "In Progress", "On Hold" and then use a row-level header for the various health symbol counts.

Source sheet data - Master

Destination sheet data - Metrics

To calculate "Active Status" checks from the "Master" sheet, you could use something like:

=COUNTIFS({Project Name}, [Project Name]@row, {Active Status}, 1)

The logic for this is "populate a count if the Project Name on the source sheet matches the Project Name in the Primary Column on the destination sheet and if the Active Status box from the source sheet is checked". The breakdown for this formula is:

• {Project Name} - the Project Name column from the Master sheet
• [Project Name]@row - the Project Name in the Metrics sheet on the same row the formula is entered on
• {Active Status} - the "Active Status" checkbox column from the Master sheet
• 1 - denotes a checked box (1 = checked, 0 = unchecked)

For the "In Progress" and "On Hold" counts, you could use:

=COUNTIFS({Project Name}, [Project Name]@row, {Status}, "In Progress")

=COUNTIFS({Project Name}, [Project Name]@row, {Status}, "On Hold")

The first part of these formulas is the same as the previous one, but these reference the "Status" column from the Master sheet, checking for "In Progress" and "On Hold" values.

I chose to break out the "Overall health" counts onto separate rows, but you could create new columns for each health color to get the same results. These formulas would be:

=COUNTIFS({Project Name}, [Project Name]@row, {Overall Health}, "Green")

=COUNTIFS({Project Name}, [Project Name]@row, {Overall Health}, "Yellow")

=COUNTIFS({Project Name}, [Project Name]@row, {Overall Health}, "Red")

Again, these formulas are looking for a matching "Project Name" from the Master sheet but instead of checkboxes or "Status" values, they're looking for symbol colors from the "Overall health" column on the source sheet.

You can reuse these formulas for any other Project that gets added to the Master sheet, as long as you list out each Project Name on the Metrics sheet as well. More information on the COUNTIFS function, @row references, and working with cross-sheet references can be found in the following Help articles:

• COUNTIFS
• Use @row to Improve Formula Performance
• Formulas: Reference Data from Other Sheets

There isn't a way to control the location of copied rows with your automated Copy Row action (they will always go to the bottom of the sheet) and, while you would still run into an issue of duplicates in the Master sheet, you could consider sorting the sheet based off of the Project Name to keep things organized. Using this separate "Metrics" sheet should then get you closer to what you're looking for.

I hope this helps!

Thanks,

Ben

Ben Goldblatt

@Jen Thompson

I just realized that the "Active Status" formula is likely not necessary as you're using the Master sheet to only show the checked rows. The "Status" and "Overall health" formulas would still apply for calculating the metrics for these rows.

Thanks,

Ben

Jen Thompson

@Ben G - Thanks so much for this great response. To ensure clarity, my problem isn't regarding building a metrics sheet or building the metrics formulas. I know how to do that. My challenge is to ensure I am capturing the metrics from the most recently copied rows from the source sheets. I need to have some sort of flag to indicate old rows (or new ones) that have been copied over. The project names could change, so that is not a stable 'unique ID' and I am not sure how best to use the last modified and created dates to indicate the most recently copied rows.

Any other thoughts?

Thanks,

Jen