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>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]desc1[/TD]
[TD="align: center"]desc2[/TD]
[TD="align: center"]desc3[/TD]
[TD="align: center"]Total (sum of cols a-c)[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
</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>
[TD="align: center"]4[/TD]
[TD="align: center"]Table Ref[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Calculation definition[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Result[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Cols[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Rows[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]Col[/TD]
[TD="align: center"]Row[/TD]
[TD="align: center"]from[/TD]
[TD="align: center"]to[/TD]
[TD="align: center"]from[/TD]
[TD="align: center"]to[/TD]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]12[/TD]
[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]23[/TD]
[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]15[/TD]
[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]25[/TD]
[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]18[/TD]
[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]27[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]120[/TD]
</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 | |
|---|---|---|---|---|---|---|
| desc1 | ||||||
| desc2 | ||||||
| desc3 | ||||||
| subtotal (sum of rows 1-3) | ||||||
| desc4 | ||||||
| desc5 | ||||||
| subtotal (sum of rows 5-6) | ||||||
| grand total (sum of rows 4,7) |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]desc1[/TD]
[TD="align: center"]desc2[/TD]
[TD="align: center"]desc3[/TD]
[TD="align: center"]Total (sum of cols a-c)[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
</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 | |
|---|---|---|---|---|---|---|---|
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: center"]Table Ref[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Calculation definition[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Result[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Cols[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Rows[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]Col[/TD]
[TD="align: center"]Row[/TD]
[TD="align: center"]from[/TD]
[TD="align: center"]to[/TD]
[TD="align: center"]from[/TD]
[TD="align: center"]to[/TD]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]12[/TD]
[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]23[/TD]
[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]15[/TD]
[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]25[/TD]
[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]18[/TD]
[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]27[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]120[/TD]
</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
