Automatically Copy Data From Multiple Sheets To A Master

Hi,

I've been playing around with Data Consolidation for a while now and can't get it to do what I want. I'm thinking that's probably the wrong way to go.

I've uploaded a sample of what I'm looking to do. Multiple people will be inputting data into different sheets but will all have the same format. What I'd like is the Master sheet to pull all the data from each sheet to show it all as one.

There will be a variety of data (numbers, words, drop downs) and the Master will be identical in terms of layout.

Some sheets will have more entries than others so if possible, only want to copy over the rows with values in them rather than the blanks as well. There will also be an unlimited number of potential rows and they will change daily.

Any help much appreciated.

May the force be with you! ;-)

how many (maximum?) sheets?.

I suspect VBA is the way to go as consolidating data from multiple sheets is very difficult to do with formulae.

Hi, thanks for replying.

I'd say the maximum will probably be ten. As for how many lines of data? Maximum would be 100 I'd say. Most likely around 20-40 rows per sheet though.

Unfortunately I wouldn't even know where to start with VBA! :-/

I've attached a file showing how you can achieve this using a few formulae.

First of all, I have inserted a new column A in all the subsidiary sheets and put this formula in A2:

=IF(B2="","-",MAX(A$1:A1)+1)

This can then be copied down as far as you like to accommodate more data being added (I've copied to row 20 in the example file, as can be seen by the hyphens, which help to show where the formula is active). This produces a unique sequential number against each record, and I've put some extra data in with blank rows to show that it is still effective in that situation. On the first of the subsidiary sheets (Anakin) I have put zero in cell A1 (coloured yellow), which is the starting point of the sequence of numbers, but in the next sheet I have used this formula in A1:

=MAX(Anakin!A:A)

which gets the highest number in the previous sheet, and so this is the starting point for the second sheet. Thus the numbers in the second sheet carry on from the first, and if you had a third sheet then cell A1 should contain this formula:

=MAX('Obi Wan'!A:A)

so then the numbers would continue into that sheet. I've coloured the cells in these helper columns blue, and if the formulae have been copied down far enough then the column can be hidden so that the sheet will look the same as before.

In the Master sheet I have inserted two new columns A and B to act as helpers, and I've also set up a small table in columns M and N, where I have put the sheet names in M2 down, and this formula in N2:

=MAX(INDIRECT("'"&M2&"'!A:A"))

which when copied down shows the cumulative number of records in those sheets. It is important that cell N1 contains zero. If you have more sheets you just need to add the name(s) to the bottom of the list in column M, and copy the formula in column N down further, as appropriate.

Cell A2 in the Master sheet contains this formula:

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

and when this is copied down it returns the sheet name where the record for that number should be found. The formula in B2, i.e.:

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

returns the row number in the appropriate sheet where the record can be found. Thus it is quite easy then to retrieve the Name field for the appropriate record, using the formula in cell C3:

=IF($A2="","",INDEX(INDIRECT("'"&$A2&"'!b:b"),$B2))

This formula can be copied across, but the b:b part will need to be changed to c:c, then d:d and so on to get the data for the other fields. Finally, all those formulae in A2:E2 can be copied down as far as you need them, in order to accommodate the amount of data that you have - I've just copied down to row 20 in the example file.

Notice that the blank rows in the subsidiary sheets have disappeared, so all the data is neatly bunched up. Columns A and B can be hidden if you don't want to see them.

Hope this helps.

Pete