Roll-up to a Master sheet

brhea110891

I am seeking a best practice for the following scenario…

There are many sheets used to manage the work and workflow for several different work areas, each managed by different groups. The workflows are setup to automate notifications and stage for deliverables. I want to roll up only a few of the tagged deliverables to a master sheet, where other columns are added to tag the deliverables in different ways, which eventually feeds reports and data sheets for a dashboard.

My question - How can I roll up selected rows from certain sheets, but only certain columns from their original sheet. I cant see how to use an Index Match.

Thank you

NickStaffordPM

One option you have is to use a universal variable across all sheets. This involves having one column on all sheets that can be used within a VLOOKUP to pull in other information from those sheets into a master sheet. One issue with this method is that you won't be able to edit anything on the master sheet; you'll only be able to edit information on each of the source sheets. The key is to have one column in common across all sheets in question. This approach works well if you only want certain columns. You can always apply a shared filter to get the specific rows you want.

Additionally, for selecting only certain rows, you could create a row report and use all the sheets in question as sources. This would allow you to select both the columns and the rows (via a filter) that you want to include. This report would have editable information from both the source sheets and the new report.

Let me know if this doesn't make sense or if you have any questions!

Paul Newcome

Do you have access to the premium add-on Control Center?

brhea110891

Thank you, Nick for the ideas. You mention that with the vlookup I won't be able to make edits to the master sheet. What if I add additional columns to the master sheet, can I edit those columns, but not the ones with info from the vlookup?

Hi Paul, we do not have Control Center, yet.

Paul Newcome

In that case, I would suggest an INDEX/MATCH or direct cell linking as opposed to a VLOOKUP. The general syntax for an INDEX match is

=INDEX({Column To Pull From}, MATCH("Text to match on", {Column To Match In}, 0))

The challenge with using cell links or formulas is that containing the formulas or links can only be updated from the source sheet(s). Other columns that do not have formulas or links can be manually updated though.

brhea110891

Thank you, I am trying to use the formula below. but I keep getting errors.

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX({Sheet1 Range 1}, MATCH(Synth@row,{Sheet1 Range 2},0)), INDEX({Sheet2 Range 1}, MATCH(Synth@row,{Sheet2 Range2},0))), INDEX({Sheet3 Range 1}, MATCH(Synth@row,{Sheet3 Range 2},0))), INDEX({Sheet4 Range 1}, MATCH(Synth@row,{Sheet4 Range 2},0))), INDEX({Sheet5 Range 1}, MATCH(Synth@row,{Sheet5 Range 2},0)))

Paul Newcome

@brhea110891 What error exactly?

brhea110891

Thank you - I decided to approach the challenge in a different way that allows flexibility and consistency needed. I am rolling up several sheets to a report, and creating formula sheet with rows for all the sheets, much easier to maintain and update as needed.