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