Sum Replicating Sheets

Joanne Lebiecki

August 06, 2018 19:45

None

Hello,

I am using the sheet replicator to create a new tab for each of our divisions. On each divisions tab (division code becomes the tab name), I have a formula to pull the region that division is in (located in cell C2 on each tab) and then I have various other data results on the sheet that I want to summarize, for example let's say cell C3 on each tab.

I would like to have a summary sheet that summarizes these data points on the sheets by the region codes. Basically, I want to sum cell C3 on each tab, but only if cell C2 on that tab is the "Europe" region, and then sum cell C3 on each tab, but only if cell C2 on the tab is the "Asian" region, etc.

Any idea on how to do that?

Date Votes

2 comments

2

Harry Lewis August 06, 2018 20:22

Hi Joanne -

Here is how I would go about that.

Let's assume that I have a report that (as you described)

1: creates sheets for each of my "Divisions" (in this case Div1, Div2, etc).

2: Lists out some values

3: Totals those values (and places that in cell E5)

4: Lists the Region to which this Division belongs

I would build my Summary sheet so that I list out my possible Regions (either hard coded like you see below or using an NL(Rows) function) and then use an NL(Columns) to list out my Divisions. This creates a matrix.

Then, I could use Excel's IF() and INDIRECT() functions to examine the Region listed on each Division sheet and bring that number in to the correct place in my matrix:

Looking like this when run:

Then, I could use Excel's SUM() function to add up a total for each Region and Jet's HIDE feature to not bother showing my matrix:

Resulting in:

I hope that gets you pointed in the right direction.

Edited by Harry Lewis August 06, 2018 20:25

Comment actions Permalink
0

Cat August 07, 2018 07:03

I would do it the same way as Harry. If you want to stay the report dynamic I don't see any other options.

Comment actions Permalink

Please sign in to leave a comment.