Create one master list from lists on multiple worksheets

The attached file shows how this can be done.

I set up a small table by listing the names of the (subsidiary) sheets in column F, from F2 downwards. Then I used this formula in G2:

=COUNTA(INDIRECT("'"&F2&"'!A:A"))-1

which finds the number of records in each sheet that will need to be copied across. Then, with zero in cell H1 (important), I used this formula in H2:

=G2+H1

which just gives a cumulative count of the number of records needed. These two formulae are copied down to the bottom of the list of sheet names.

I used column A to determine which sheet name to bring the data from (equivalent to your column C), with this formula in A3:

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

Copy this down until you start to get blanks (this occurs after the row given by the highest number in column H plus 2). Then this formula can be used in B3:

=IF($A3="","",INDEX(INDIRECT("'"&$A3&"'!A:B"),ROWS($1:1)-INDEX($H:$H,MATCH($A3,$F:$F,0)-1)+2,COLUMNS($B:B)))

I've written this so that it can also be copied into C3. Then both formulae can be copied down to give you the composite list you required.

If you have more sheets, just add them to the bottom of the list in column F and copy the formula in G and H down as required. If you have more data, just copy the formulae in A:C down as required.

Hope this helps.

Pete

Gents - both work nicely, many thanks!
I should have thought about this when I originally posted, so apologies, but what if the ID field on the subsidiary list sheets (list 1, list 2 etc.) sits in column D? I'd still like to return it to my left-most column on the Master List (column B in Pete's suggestion and column C in mehmetcik's)..
Many thanks in advance!

It would be handy if you attached another sample workbook which shows the exact layout of data that you have, so we can adjust the formulae as required.

Pete

Hi Pete
Thanks for your reply. My dataset is more like the attached example.
As you'll see, the unique ID column varies from list to list. I have coloured the column yellow in this example.
Bringing the description through is very useful but there will be other columns I need to return on the master list as well. I was thinking of using a combination of IF, VLOOKUP, INDEX/MATCH, ISNA/IFERROR etc. to scan through each worksheet one by one and return values - IF unable to locate the ID on one sheet then check the next.. do you think that'll work or should I be looking to use something else?
Really appreciate your help with this!
Thanks
Tom

I've moved the table across a few columns so you have room to bring some other data across - this obviously changes the cell references in the formula in A3.

I've changed the formula in B3 to this:

=IF($A3="","",INDEX(INDIRECT("'"&$A3&"'!A:H"),ROWS($1:1)-INDEX($L:$L,MATCH($A3,$J:$J,0)-1)+2,MATCH(B$2,INDIRECT("'"&$A3&"'!A2:H2"),0)))

The main changes are show in red. The MATCH part now matches the title on row 2 with the appropriate title within each sheet, so your Item No. can be in any column. However, the titles have to be an exact match - I had to add a full stop to two of the Item No headings. I've also changed Own Depot and Curr Depot to make them all the same throughout.

With this formula you can have whatever headings (and in any order) that you like, and you can just copy the formula across and down. I've done this by adding Own Depot to D2 and putting the formula in D3, but you can try it out for yourself by adding Curr Depot to E2 and copying the formula to E3 and down. Do the same for other fields that you want to bring across, but you must ensure that the headings match in all sheets.

Hope this helps.

Pete

Hi Pete
Thanks again for your help. It works nicely in the example workbook but I get #N/A values in column B when I copy the formula across to my 'live' file.
I have attached another example file, this one reflects the structure of my 'live' file 100%. Maybe there's something obvious I'm missing?
The only change is my ID field is now called 'Object No.' and not 'Item No.'. I have ensured the subsidiary worksheets match so guess that's not the issue?
Thanks again
Tom

You now have your tables starting on row 1 in the subsidiary sheets, so you need to make two changes to the formula in B3 of the consolidated sheet, like this:

=IF($A3="","",INDEX(INDIRECT("'"&$A3&"'!A:H"),ROWS($1:1)-INDEX($Q:$Q,MATCH($A3,$O:$O,0)-1)+1,MATCH(B$2,INDIRECT("'"&$A3&"'!A1:H1"),0)))

This should copy down automatically. You can also copy this across, although you will need to change some of the titles in the subsid sheets, as previously advised.

Hope this helps.

Pete

Hi Pete
Ahh I should have spotted that! Thanks a lot!
There's one final step that would completely finish this one off for me how could we modify the formula to not return zeros (0) or #N/As? I've tried an IFERROR round it but can't quite seem to get it to work..
Sorry about this haha! Really appreciate your help!
Thanks
Tom

If the cell from which you are returning data is empty (blank) then Excel will return it as a zero. The data that you are returning all seems to be text rather than numbers (yes, I know some of the data consists of numbers only, but they are really text values rather than numeric, like Serial No. - you wouldn't think about doing some arithmetic on those values, like dividing by 2, as it wouldn't make sense). That being the case, you can amend the formula in B3 to this:

=IF($A3="","",INDEX(INDIRECT("'"&$A3&"'!A:H"),ROWS($1:1)-INDEX($Q:$Q,MATCH($A3,$O:$O,0)-1)+1,MATCH(B$2,INDIRECT("'"&$A3&"'!A1:H1"),0))&"")

then copy across. If you were returning numeric data (like a date or a quantity) then you would have to use a construct like IF(formula="","",formula), which is a bit messy. You could also apply a custom format to those cells which would suppress the display of zeros, but then zero could be a valid return for a numeric field.

The #N/A errors occur when the data can't be found - this is likely to be caused by that particular field not being present in a particular subsidiary sheet, and to avoid that you can wrap IFERROR around the INDEX formula, so that in B3 it becomes:

=IF($A3="","",IFERROR(INDEX(INDIRECT("'"&$A3&"'!A:H"),ROWS($1:1)-INDEX($Q:$Q,MATCH($A3,$O:$O,0)-1)+1,MATCH(B$2,INDIRECT("'"&$A3&"'!A1:H1"),0))&"",""))

Copy across as before.

Please note: If that takes care of your original question (and, indeed, all the other questions since), please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Hope this helps.

Pete

Thanks, Pete. The IFERROR one is just what I was looking for. You've been a massive help today.. Really appreciate it.
Regards
Tom

I also need to create a master list that will update if i update one of the crew list.