Text data consolidation from multiple worksheets

Hi all,

I am looking for some assistance with consolidating text data from multiple sheets into one master sheet. The tables in each sheet have different headings but there is one heading (basically like a primary key) in each sheet which is the same and it is this that I would like to automatically consolidate.

I believe this could be achieved with the MATCH and INDEX function but I am not sure how to achieve this over multiple sheets. I have attached a workbook with dummy data and it is the output in the summary that I am trying to achieve. Ideally as much of this as possible needs to be automated i.e. If I add another Fruit in the Fruit tab, the formula in the summary tab will automatically update with the new value.

I hope that makes sense and any assistance would be greatly appreciated.

Many thanks
Tim

The attached file shows how you can achieve this. I've inserted a new column A in each of the subsidiary sheets, with a formula in A2 which is copied down beyond your data (to row 10). This sets up a sequential count of records, running from each sheet to the next by means of a formula in A1 of each sheet which looks back at the previous sheet (set to zero in the first sheet). The helper columns are coloured blue - you can hide them if you wish, but ensure that they are copied down beyond where you expect your data to cover, to allow for future expansion.

In the summary sheet I've set up a small table in columns J and K which records the maximum number reached on each sheet. If you have more sheets then you can just add the names of the sheets to the bottom of column J and copy the formula in K down as required. I've also used two helper columns in this sheet, one to record the row number where the record exists and the other to record the sheet name. Then an INDEX formula in column C can retrieve the appropriate data, and this will automatically adjust if you add more data to one or more of the sheets.

Hope this helps.

Pete

Hi Pete,

Cheers for your help. I will have a look at this today and see how I get on with implementing it into my real world workbook.

Thanks again
Tim

Hi Pete,

Sorry, I completely forgot to follow this up to let you know that this worked really well for me.

Many thanks for your help.
Tim