Building a Master Sheet using Copy Rows
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.
Below is an example.
Help Article Resources
Check out the Formula Handbook template!