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:
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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.
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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
B | C | D | E | F | G | |
---|---|---|---|---|---|---|
2 | a | b | c | d | ||
3 | desc1 | desc2 | desc3 | Total (sum of cols a-c) | ||
4 | 1 | desc1 | 1 | 2 | 3 | ? |
5 | 2 | desc2 | 4 | 5 | 6 | ? |
6 | 3 | desc3 | 7 | 8 | 9 | ? |
7 | 4 | subtotal (sum of rows 1-3) | ? | ? | ? | ? |
8 | ||||||
9 | 5 | desc4 | 10 | 11 | 12 | ? |
10 | 6 | desc5 | 13 | 14 | 15 | ? |
11 | 7 | subtotal (sum of rows 5-6) | ? | ? | ? | ? |
12 | ||||||
13 | 8 | grand 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.
B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|
4 | Table Ref | Calculation definition | Result | ||||
5 | Cols | Rows | |||||
6 | Col | Row | from | to | from | to | |
7 | a | 4 | a | 1 | 3 | 12 | |
8 | b | 4 | b | 1 | 3 | 15 | |
9 | c | 4 | c | 1 | 3 | 18 | |
10 | a | 7 | a | 5 | 6 | 23 | |
11 | b | 7 | b | 5 | 6 | 25 | |
12 | c | 7 | c | 5 | 6 | 27 | |
13 | a | 8 | a | 4 | 12 | ||
14 | a | 8 | a | 7 | 23 | ||
15 | b | 8 | b | 4 | 15 | ||
16 | b | 8 | b | 7 | 25 | ||
17 | c | 8 | c | 4 | 18 | ||
18 | c | 8 | c | 7 | 27 | ||
19 | d | 1 | a | c | 1 | 6 | |
20 | d | 2 | a | c | 2 | 15 | |
21 | d | 3 | a | c | 3 | 24 | |
22 | d | 4 | a | c | 4 | 45 | |
23 | d | 5 | a | c | 5 | 33 | |
24 | d | 6 | a | c | 6 | 42 | |
25 | d | 7 | a | c | 7 | 120 |
<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