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.
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}
Thank you for your reply.
So I guess there is no way to make Excel name a new table I create with a sequence number (I do not wish the tables to have the volunteer names). So if the last table I have is volunteer9, if I copy it and then paste it to create a new tables there is no way for excel to call it volunteer10 instead of that other number it assigns automatically?
The other question is, in the "total income" column in the main table, is there no way to help excel auto fill the formula with the sequence table name rather than entering the exact same table names in all the rows? In other words, I do not wish to have volunteer1[[#Totals],[Total income]] in all rows, just in the first row, the second row should have volunteer2, the third row volunteer3 and so on.
Thank you
Thank you so much for all the detailed help. I did not noticed the attached file in your previous email. Thank you for that!
I have tried to search for more information about VBA, but that is obviously something I would really need to study before I can even try to do it.
Sukhayanti wrote:So I guess there is no way to make Excel name a new table I create with a sequence number
Excel seems to be something of a law unto itself when it comes to generating Table names when one copies and pastes. I think you either have to rename them manually or use VBA to run through the ListObjects collection renaming them in sequence.
Sukhayanti wrote:In the "total income" column in the main table, is there no way to help excel auto fill the formula with the sequence table names
That is done in the file I attached for the Start Date, End Date, Total income and Name columns.
By building each table name as a text string I was able to incorporate your record number ['#] into the name and INDIRECT returns the corresponding reference.
= INDIRECT( "Volunteers" & [@['#]] & "[[#Totals],[Total Income]]" )
Note:[@['#]]is the structured reference to the '#' column within the current record