Forum Discussion
Creating tables and linking data
The attached file uses the INDIRECT function to reference parts of the sub-tables from a text string which is constructed to define the data to be returned. If you wish to refer to your volunteers by name, you would need to build a list of Table Names based upon the volunteers' names.
As a comment, I would prefer to see all the detail tables combined into a single table with no subtotal rows. Then all the totals could be calculated within the summary table using MAXIFS, SUMIFS etc.
.png){kind=link}
You are going to have some problems simply because of your approach to storing data. By using separate tables for data relating to each volunteer, rather that a single table in which the volunteer number (or name) is held as a field, you make aggregation and lookup difficult.
One possibility is to build the structured reference to the data you require as a text string and then use INDIRECT to turn it into a reference. This would require something of the form
= INDIRECT( "Volunteer" & [@['#]] & "[[#Totals],[Amount]]" )
if your tables are literally named "Volunteer1" etc. or to pick up the sub-table name from a list 'directory' in a more general case.
= INDIRECT( INDEX(directory, [@['#]]) & "[[#Totals],[Amount]]" )
p.s. The fact that the formula propagates down is a good thing. Formulas that change from record to record suggest that the data is not structured correctly.