How to merge multiple tables into 1 master table?

I will be having an identical table (format / column names / etc) in multiple sheets. In one sheet I would like to take the entries made in all the other sheets and have them all listed in this new master table.
Scenario explanation:
I would have a separate sheet for each month and in each of those sheets I will be recording the sales completed that month. But then in the master sheet I would like to have all sales listed together.
Everywhere online seems like it will answer this question but it never seems to be this situation - they tend to be talking about merging table columns (so adding additional columns) and stuff.
So sorry if this is a simple/stupid question
Thanks in advance

It can be done with a few formulae, but it would help if you attached a sample Excel workbook so we can see exactly what data you want to bring across to the master sheet.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Pete

So I only have one sheet made and it's one month and it shows how the sales will be recorded. I have not created additional months or made a spot for the master sheet because I did not want to go further if it would not be possible to do this. Would the one sheet I made suffice?

So I followed your steps - hopefully it worked.

I presume you want to show the yellow area(s) i.e. columns J to Q from each sheet in the master sheet.

How will your Month sheets be named? You could just use Jan, Feb, Mar etc. i.e. 3-letter abbreviations of the month name, or you could use the full month name, but then you will only be able to have one year's data in a file, and you will need to start a new file in a different year. Or you could have Jan-17, Feb-17, Mar-17 etc. for the sheet names, and then you will be able to accommodate many years in one file. I need to know the format of the names as I will be accessing the data from them.

How will your master sheet look? Will this be exactly the same format, with column C showing the summary information, and if so will you need the blue areas for time tracking? (I'm not sure what that is there for).

I'm thinking of adding one more column to the Month sheet which will identify unique records, and will do this from one sheet to another in sequence. Then the master sheet will be able to pick up on these sequential numbers to collate all the information in one sheet. The master sheet will also include a lookup table of sheet names and records per sheet, and will have two helper columns. I hope this is a satisfactory approach.

Hope this helps.

Pete

Yah, I will be changing the names to Jan17, Feb17, etc.
However, I cannot guarantee that this will be the final format.
And yes I was talking about the yellow portion, however I will be doing the same thing for the blue portion.
I used this sheet to give an idea of why I need to do this. But I would really just like to learn the concept on how to do it. I'm not asking you to do it for me. I would like to learn how so I can do it on my own in the future because this is a concept I will be using a lot.
And having a unique identifier column is fine I can call it TX# or something.
Anyways, hopefully that all made sense.

So, I guess teaching is not something you're willing to do?

Sorry for the delay in getting back to you. I'll take you through the initial steps.

The idea is to set up a sequential number to identify records which match the criteria, and to pass that numbering across to the other monthly sheets, but it would help if you first of all renamed the Month sheet to Jan-17. Then you can put zero in cell A8 and this formula in A9:

=IF(J9="","-",MAX(A$8:A8)+1)

Here the criteria is just that column J (yellow Date) has something in it, but if you copy this down to A68 you will see those sequential numbers appear where you have data in the table. Incidentally, you can always hide these formula/values by applying a grey font colour after you are sure everything is working as it should. I would also suggest putting this formula in cell A1:

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

and this will give you the name of the sheet that it is on.

Then you can just Ctrl-drag the sheet to produce a copy, and rename it to Feb-17. In this sheet we don't want the counting to start from zero in cell A8 - rather, we want it to start from where the last sheet had got up to, so to help us with that this formula can be used in cell C1:

=TEXT(DATEVALUE("1"&A1)-1,"mmm-yy")

and this returns the filename of the previous sheet. We can then have this formula in A8 of the Feb-17 sheet:

=MAX(INDIRECT("'"&$C$1&"'!A:A"))

and you can see that the numbering will carry on consecutively for the matching records. This sheet can now be copied again and renamed as Mar-17 to confirm that everything is working as it should. Obviously, the next stage then is to keep doing this until you have sufficient monthly sheets to meet your needs (clearly, the data in the yellow area will need to be deleted and the appropriate data put in instead).

Let me know when you have got that far.

Hope this helps.

Pete

Hey thank you so much for that! I really appreciate it
Sorry it took me a while to respond also, just had surgery on Tuesday - I may not be able to get to this for a few more days because of the pain killers taking away my thinking capabilities lol.
But, I wanted to thank you for taking the time to write out some instructions This is something I've been trying to figure out for a while.
I'll post here again if I have any problems - thank you.

I hope you start to feel better soon.

The instructions above just relate to the first part, which is concerned with setting up the monthly sheets such that we can extract data from them sequentially. The next part will concern setting up the Summary sheet so that we can gather all the data together.

It's getting a bit late here now, though, so I'll continue with that tomorrow (or more likely on Monday, as the weather is meant to be quite nice tomorrow!)

Pete

Moving on to the next part then, we need to set up a Summary sheet, and the easiest way to start is to Ctrl-Drag the Jan-17 sheet to produce a copy, and then to rename it as Summary. You can also delete all the formulae that are in column A of the Summary sheet, as we will need different formulae here.

Having generated those sequential numbers which pass on from one monthly sheet to another, we also need in the Summary sheet to be able to generate sequential numbers on consecutive rows so that we know which record to bring across. We can do this quite easily using the ROWS function, with one of the rows anchored, so that as it is copied down the other row changes. For example, put this formula in a blank cell somewhere:

=ROWS($1:1)

and then copy this down a few rows - you will see that it generates 1, 2, 3, 4, 5 etc. on successive rows. A similar effect can be obtained from the formula =COLUMNS($A:A) if that is copied across.

We also need to know which sheet a particular number lies in, and to do this we need a lookup table which will go in column T and U. Put the first sheet's name (i.e. Jan-17) in cell T2 of the Summary sheet - you need to enter this as text, otherwise Excel will try to convert it to a date, so you can either format the cell as text and then enter the value, or enter the value with a preceding apostrophe, like 'Jan-17. We can then generate successive months automatically by putting this formula in T3:

=TEXT(EOMONTH(DATEVALUE("1"&T2),1),"mmm-yy")

and copying this down as far as you think you may need. We then need a zero in cell U1 and we can use this formula in U2:

=IFERROR(MAX(INDIRECT("'"&T2&"'!a:a")),"")

which again can be copied down. This then gives us the largest number used within each monthly sheet, so that we can identify which sheet a particular number can be found in. We can do that by putting this formula in cell A9:

=IF(ROWS($1:1)>MAX(U:U),"",INDEX(T:T,MATCH(ROWS($1:1)-1,U:U)+1))

and copying this down (note the use of the ROWS($1:1) term here). It would also help if we knew which row in that sheet the record occurs in, and we can find that out by putting this formula in cell B9:

=IF(A9="","",MATCH(ROWS($1:1),INDIRECT("'"&A9&"'!a:a"),0))

and copying that down. Note that you might want to set the foreground colours to the same as the background colours for these two columns, as previously advised.

So, all we need to do now is to generate the composite sales log - I'll leave that for another night.

Hope this helps.

Pete

Hi,

I'm not sure if you are still monitoring this thread, as I've not heard back from you, but just to finish this off we need to pull the information from the relevant sheet and from a particular row and put it in the yellow area on the Summary sheet. We can do this with one formula, which you can put in cell J9:

=IF($A9="","",INDEX(INDIRECT("'"&$A9&"'!j:q"),$B9,COLUMNS($J:J)))

This formula can then be copied across into K9:Q9, and it should then fill down the columns of the table. You might need to make the table bigger, to accommodate more data, but if so you will also need to extend the formulae in columns A and B.

And that's about it ...

In being able to quote the various formulae to you, I have obviously been building up the solution to this as I go along, so I shall attach the file so you can see it working. In the Feb and Mar sheets I amended the dates to suit, and so in the Summary sheet you can see that the data has come from the appropriate sheet. I haven't done anything to columns C to I in any of the sheets, so I'm not sure if they need some amendment. I have an April sheet with no data in it, so you could use this as the template to create other monthly sheets and rename them as appropriate.

Hope this helps, and I hope you are well on the road to recovery.

Pete