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.
Maybe it would be more clear what I'm trying to do if the tables were in English.
I had one Excel table, the problem for me with that table was that one volunteer might work at different departments during his stay with us. So it was really awkward the way the sheet looked like and it wasn't working for me at all.
I would send a screenshot but I can't at the moment since I'm on my phone.
I created the extra sub tables so that each sub table includes the detailed information about which department he was at and for how long and so on and the main table just has the summary of expenses for each volunteer.
It looks much cleaner like that and easier for me to organize the data
.png){kind=link}
Yes, sadly my language abilities are limited, so English would have been clearer. But the main point still holds---the database on a single table and then the various reports or outputs extracted from that really works better.
Screen shots are not (in general) anywhere near as helpful as actual spreadsheets--just make sure that personal or proprietary info is not included.
If you need any reassurance, your English is faultless.
The many to many relationship between volunteers and departments could be challenge but it is difficult to assess without seeing a representative dataset.
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.