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}
Thank you both for trying to help with this. I am sending you the spreed sheets. You will see the one I used this year. I basically designed my own "table". My problem with this version and with trying to crate a real table in excel is that I cannot have more than one row per volunteer and since some of them volunteer in more than one department I need that feature. That is why I was thinking of creating a table per volunteer.
Please know that my knowledge of Excel is very minimal so unless you do not explain to me any formulas you send, I might not know what you are talking about
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.
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
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
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.