Automatic function that copies items from multiple individual sheets to master sheet

Hello,

I have multiple sheets labeled Anderson- Thein with a master sheet labeled as NE Region. I need a formula or function that will allow any information placed in the rows from the sheets to be seen in the Master Sheet NE Region. All of the information from that row should transfer to the master sheet.

I used a table for organization but it seems when i sort a column, the row does not sort with it. Im looking into formulas to correct that as well.

I am an absolute novice. So any help, or suggestion are welcomed!

Take a look at this thread from a couple of days ago:

http://www.excelforum.com/excel-gene...s-at-once.html

It's very similar to yours (gets data from 5 sheets to a master sheet).

Hope this helps.

Pete

Hi Pete,

I have been looking into a couple of threads where people have trouble copy over multiple sheets to a master sheet. I see that you have listed the formulas in Column A. Ive copy and pasted what you used on your last post but I cant seem to get it to match up.

Am I placing the formula at the same level of the header, or am I starting it at the cell where the first row of data would be entered?

Insert a new column A in each of the sheets from Anderson to Thein. Then in the Anderson sheet you can put this formula in A8:

=IF(D8="","-",MAX(A$7:A7)+1)

and copy this down as far as you think you might need it. Put zero in cell A7 (and colour it with a yellow background for emphasis). Then you can copy that column A into all the other columns A of the other sheet, and then you need to amend A7 on each sheet with a formula which looks back to the previous sheet in turn. So, in A7 of the Cereo sheet you would have this:

=MAX(Anderson!A:A)

and in the Jouppi sheet you would have this:

=MAX(Cereo!A:A)

and so on for all the other sheets up to the Thein sheet, where you would have this:

=MAX(Stout!A:A)

As you already have the sheet names listed in the Intro sheet, you could use this as the table to record the maximum number in each sheet. Put zero in cell E1, and this formula in E2:

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

Copy down to E10.

In the NE Region sheet you will need to insert two new columns A and B, and then you should be able to complete the composite table as in the other attachment.

Hope this helps.

Pete

I went ahead and completed the file for you - attached.

Note that the first formula I gave you above is checking for the hire date being completed - I figured this was a safe option to determine each record.

Hope this helps.

Pete