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.
As Peter Bartholomew has already said, you may be making your task more difficult than it needs to be. A common mistake (and I hope you'll excuse my use of that word) in using Excel happens when the design or layout is based primarily around the ultimate output or report desired. I don't know that this is what you're doing, but it sounds like it could be. You say you're trying to design a "simple database"--but a true simple database would be all on one spreadsheet. You'd collect your data there, and then devise ways (using Pivot Table or Power Query or VLOOKUP, INDEX MATCH, etc) to display selected data in useful fashion.
In other words, think of the input and collection of data as a task altogether separate from the output. Excel contains a number of powerful tools to manipulate data; we hinder it when we try to format the raw data in output layouts.
Again, that may not be what you're doing....but it kind of looks like it. If you could upload the raw spreadsheets, some of us here might be better able to help with suggested alternatives.
.png){kind=link}
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
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