Forum Discussion
Creating tables and linking data
Hi,
I am trying to create a simple database on excel since I really do not need anything complicated however I am having difficulties with the auto fill feature.
I have created a main table and sub tables.
The sub tables are named volunteer1, volunteer2, volunteer3 etc. At the moment I created 9 such tables but with the course of the year, I might reach an amount of 55 tables or more. My first question is, is there a way to set up the naming of the new tables in such a way that Excel automatically names the tables by a sequence of numbers. So if, for example, I copy the volunteer9 table and then paste it on the sheet and in that way create a new table, can I do it in such a way that Excel will automatically name it volunteer10. Right now what happens when I do this is that it gives it another strange number volunteer914. Is there another way to duplicate a table within the same sheet and have it auto fill the table name in a proper sequence?
My next question is about connecting cells from another table:
I need to connect one cell from my sub tables to a column in the main table. I have managed to connect the cell that I wanted to the main table but when I do so, it auto fills the rest of the column with the exact same formula which is: =volunteers1[[#Totals],[Végösszeg]]. What I would like him to do is change the name of the table automatically to volunteer2 and so on until volunteer9 and more as I add new tables to the sheet. Is that possible or do I need to do it manually every time I create a new table?
Thank you in advance
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.
10 Replies
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.
- Thank you for your answer.
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 dataYes, 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.
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.
.png){kind=link}