Creating tables and linking data

[Sukhayanti]

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

[#2]

@Sukhayanti 

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.

[#3]

@Sukhayanti 

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.

[#4]

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 data

[#5]

@Sukhayanti 

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.

[#6]

@mathetes 

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.

[#7]

@Peter Bartholomew 

@mathetes 

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  

[#8 · Accepted solution]

@Sukhayanti 

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.

[#9]

@Peter Bartholomew 

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

[#10]

@Sukhayanti 

---

@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

[#11]

@Peter Bartholomew 

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.