Copy Rows to Build a Master Sheet out of Individual Sheets

Jen Thompson

I have ~40 project info sheets that are structured with columns to populate roll up reports for a portfolio-level executive 'dashboard'. These info sheets also have sheet summaries referencing the 'active status' and populating a dashboard with that content.

I want to on my executive dashboard capture metrics (but can't do metrics on a report):

Total # of projects (40)

Total # with Red / Yellow / Green - dyanmically changes week to week

Total # on hold

When the project info sheets are updated (weekly) the project managers can either overwrite the existing row (if they don't want to preserve the history) OR they can add a new row and toggle the 'active status' checkbox to something else.

In order to capture the metrics, I thought I'd copy the 'active status' row from the individual 'Project Info' sheets to a "Master Sheet" which would aggregate all of the projects into a single sheet from which I can calculate metrics. Issues with this... there is not a way to identify a distinct row for each project, so the "Total # of projects" just keeps growing week on week. The workaround I thought of was to capture the Sheet ID and push that as part of the row copy. That does get me a unique count of projects, but it is cumbersome to project managers to have to populate the sheet ID (requires too advanced knowledge of smartsheet and may not be scalable).

Then once I get the row in there, even if I did have a 'project/sheet ID' I am not sure how to distinguish one 'active status' row from the next to capture count of RAG status and on hold without it calculating previously added 'inactive statuses'. Is there a way to automatically look for duplicates and remove/tag those that were previously entered (or tag the new unique ones)?

Note - I do not want to do cell linking - that is unsustainable.

Example below.

David Joyeuse

Hi Jen.

So you do not want to do some cell linking. IMHO, I think that's the best way to do it, but requires some help from other cells as you only have to do it once a new project is created. Limiting it to 3/4 cells max that sums up your new project should be enough for what your are trying to do (like project status, price, stage project is on...).

But since you don't want I'll give you a tip on how to get rid of old lines. It will require a few helper columns though.

First, you're gonna need the date created column. This column will automatically display the date on which the column was added.

Second you're gonna need a column that I'd call "Old lines". Make it a checkbox.

Use this formula within it:

=IF([Date created]@row=INDEX([Date created]:[Date created], MATCH([Project Name]@row,[Project Name]:[Project Name], 1)),0,1)

That should check the box if the line is not the most recent line.

Then, create an automated rule that works daily (or according to your wishes) that will automatically moves rows with the column "Old Lines" checked to another sheet called bin (or whatever, but it's always useful to have a bin sheet somewhere in your workspace). Limiting your "roll up sheet" to the newest lines. If you automatically copy rows a few minutes earlier than removing the old lines, it should work smoothly. Like copy rows at 7:58 am, then remove old lines at 8:00 am.

Hope it helped!

Jen Thompson

@David Joyeuse - Thank you for this suggestion. And I appreciate your opinion about cell linking. While I think cell linking is a solid feature, the dispersed group of PMs that work on these projects have a range of skills and capabilities that require less manual intervention (even when it comes to cell linking).

Your solution is a good start. I tested it out on the sample files, but it keeps the 'Archive' (same as Old rows) checked when a new row is added and when it moves over that information is retained because it is a copy of the information. Additionally, the project name is not guaranteed to always remain static, so if the name changes, then it may not work - correct?

The only rows that should have the "Archive" column checked are rows 10 on the project info and 12 on Master.

Thanks!

David Joyeuse

@Jen Thompson I know how you feel about your PM, I have that same type of guys that are too lazy to even do some cell linking :P

It seems I misunderstood a bit, and I don't like the part of PM editing existing rows OR adding new. It will make it quite impossible to sort in the end.

As a whole, I tend to think people are dumb and lazy, so it's best to make it as easy as possible for them (and basically, do the job for them, which in the means they are nearly useless).

So you have some sheet summaries on every project. If those are formula created, then I would strongly recommend you to get rid of ANY intervention from your PM. Have them work on their project sheet. But NOT on your sheets. Make sure summaries are automatically copied to your master sheet each time they are modified and stick to it. This will make it easier.

If project Name change during the process, yes it'll be a problem that we'll have to correct. If your project change name do you still keep the Project 1 (New Name) structure or not? If Yes, I could come up with something. If name change from one thing to another. We're gonna need some help with another sheet refering project names and whatever. Usually, working with INDEX/MATCH or VLOOKUP requires to some some unique datas within sheet to make it possible, otherwise, it's a bit tough.

Regarding the archive Status, it looks like I got it backwards as it should only archive the old lines. Not the most recent ones. I'll have to double check that with a COLLECT.

Do you want to keep the old lines on your master sheet? Or just the newest ones? If you want to keep oldest ones just to calculate the number of occurences, we can do that as well from another sheet and keep the "active lines" clean so you can have a quick glance or where you're at with your projects.

Jen Thompson

@David Joyeuse - to answer your questions:

• I do use formula generated sheet summaries to capture the most recent 'active status' to display on a dashboard, but copying the rows to the new sheet doesn't copy the sheet summary, and it would overwrite every time a new row were copied, if that was a feature. So, I'm not sure how that would help.
• the name could change throughout the project multiple times. Ideally it would be the same name, but, a product launch project might change the name multiple times to represent the internal product name to the commercial product name.
• Yes, I'd like to flag the old lines... I don't necessarily need to move them out of the master sheet, but rather (at least) have a flag to exclude them from the metrics. I may do a 'purge' once in a while, but don't necessarily need to have that automated.