Sum fields in table1 based on a definition of that sum in table2

chris3131

New Member
Joined
May 19, 2015
Messages
9
I have a table of data similar to this (lets call it "table1"), containing it's own row and column references (in red - ie. different from the Excel col & row references) and also containing subtotal rows and subtotal columns eg:
BCDEFG
2abcd
3desc1desc2desc3Total (sum of cols a-c)
41desc1123?
52desc2456?
63desc3789?
74subtotal (sum of rows 1-3)????
8
95desc4101112?
106desc5131415?
117subtotal (sum of rows 5-6)????
12
138grand total (sum of rows 4,7)????

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1



For various reasons I don't want to simply include sum formulae within the table (@locations of '?') to complete the subtotal rows and columns. I need to do this calculation in a seperate table on a seperate worksheet, based on the specific row/col references from table1; I will then lookup those values to bring them back into "table1".

I was thinking "table2" would look something like this, whereby the field requiring the formula (? in table1) is defined as the 'Table Ref' and this is followed by a definition of what the sum formulae needs to do. eg. datapoint a4 needs to give the sum of a1:a3; datapoint d3 needs to give the sum of a3:d3; and in the case of datapoint a8 this needs to sum a4 and a7, although in this case (all rows in yellow) I don't mind if these are on multiple rows since I can use a SUMIF to bring them back into table1.
BCDEFGH
4Table RefCalculation definitionResult
5ColsRows
6ColRowfromtofromto
7a4a1312
8b4b1315
9c4c1318
10a7a5623
11b7b5625
12c7c5627
13a8a412
14a8a723
15b8b415
16b8b725
17c8c418
18c8c727
19d1ac16
20d2ac215
21d3ac324
22d4ac445
23d5ac533
24d6ac642
25d7ac7120

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2



So my question is what is the formula I need to use in the Result column? I'm sure its a combination of INDEX, MATCH and INDIRECT but after hours of trying, I cant quite fathom it.
Also remember that table1 is on a seperate worksheet to table2 so this needs to be taken into account in the formula. I am also very happy to make use of range names in table1 if that makes the formula more managable.

If you have a better suggestion for how table2 could be presented then I'm very happy to change that. What is key is:
a) that I have a reference to the datapoint in table1
b) that the calculation definition can be defined by the user in a logical fashion
and c) that the result of the formula is presented alongside the datapoint reference in such a way that is can be brought back into table1 as a total.

many thanks in advance
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,330
Members
409,863
Latest member
stacy09
Top