Combine Cross-Referenced Sheets

ker9

Hi - I have 6 Summary sheets that are cross referenced to 6 Regional sheets. These have formulas gathering totals by person. Sort of mimics a pivot table. Works great for stacked bar charts.

I'd like to combine all the totals for each person in one sheet. People might appear on more than one Summary sheet. The names may change (add/delete). The Summary sheets are using a formula to get the distinct names from the Regional sheet.

Unfortunately, a report brings in duplicate names and I'm looking for one row per person with totals from all Summary sheets.

Looking for suggestions on best method to get all the names, no duplicates, and keep them updated.

TIA!

Mike TV

@ker9

Sometimes you build a sheet(s) only to realize too late that something you require your sheet to do just is not possible with the way your sheet(s) is currently built. You then may have to go back and re-design a whole sheet or sheets just to make something work which would not work otherwise due to the way it was built.

ker9

@Mike TV - Happy to rebuild if you have a suggestion on how to make this work. I need 6 Regional sheets due to row limits - combining them is not possible.

ETA: Once we realized we had too many rows for one sheet, it was logical to divide by region.

I'm looking at using DataMesh to update all names and then linking, however, this is pulling from a sheet that has all employees and I really only need a subset of that but I think it's probably the best option. Considering automation to remove the rows I don't need.

ker9

I can use DataMesh to get all the names I need, but I haven't figured out how to pull together the data from the 6 sheets corresponding to each person (may have data on multiple sheets). (@Paul Newcome - I think of you as the formula guru/genius - any ideas?)

ETA: Looks like Collect might be the answer but I'm getting Invalid Operation.

ETA: I think Index/Match + Index/Match is going to work. Open to better/more efficient options. This needs "IFERROR,0" in the formula. IFERROR(Index({range}, MATCH(email@row,{range},0)),0)

ETA: I get an error of too many cross sheet formulas :(

Paul Newcome

I would suggest a report to bring in all summary sheets then grouping by person. From there you can insert the report summary fields, and you should be good to go.

ker9

@Paul Newcome - I get multiple rows for each person in a report and I'm trying to get to one row per person in order to chart the data.

Paul Newcome

Right. But when you group in a report, the chart will pull in the groupings as opposed to the individual rows.

ker9

@Paul Newcome - I'm sure I'm missing something. My stacked bar is by Person (vertical) and then Dollars (horizontal) by Level (stack). I have not been able to design a report that would give me that chart.

ETA: I think I need a pivot to get the levels in columns instead of rows?

ker9

I think I would need a pivot, which I can do but it's about timing. If I create a report with all sheets combined and then create a pivot, the pivot can only update once per hour (from a report). When people are making updates, we need it closer to real time.

Paul Newcome

Are you able to provide a mocked up example of what you are trying to accomplish?

ker9

@Paul Newcome - I think I finally figured it out (?) - The Levels are in one column so at the end of the sheet I added columns to represent the levels and formulas to get the data per level and now I can chart it. I'll hide those helper columns. Thank you!

ETA: I was trying to avoid using a report because they break if the column name changes.

ker9

I had to go back to the metric summary sheets and then use a report to combine them (I was trying to avoid reports). I ran out of cells if I did the metrics in the main sheets.