Consolidating worksheets

I have 26 worksheets to consolidate. I want to be able to have all worksheet
names on the consolidation sheet. I also want certain information off each
of these sheets to be entered on the consolidation sheet. I have tried the
help, and can get the information from one sheet to the YTD consolidation
sheet, but, when I try to copy the cell, I get the same info there as well.
I don't want to have to enter all of these manually, and there must be a way
that excel does it.

Any help would be appreciated.

Thanks

Joe

Without using VB code, and assuming that the total number of lines in all worksheets does not exceed 65536, that you do not have nor want duplicates, and that your names are in column A.

Copy the names column from each sheet to a spare worksheet to form a long column.
If you have a main sheet that you want data from other sheets added to copy it's names to columns A AND B.
If you are starting a brand new consolidation sheet then ignore column B.

Sort over column A then over column B

In C1 put
=IF(B1<>"","",A1)

In C2 put
=IF(OR(B2<>"",A2=A1),"",A2)
and formula copy this to the end of your data.

Select column C and Copy, then Paste Special = Values back over itsself.
Delete columns A and B

You now have either a complete list of names or a list of names to be added to your main sheet, in which case select and Copy these names to the end of your main sheet data.

For each column of data required from other sheets, in row 1 of that column put a lookup something like:

=IF(ISERROR(VLOOKUP(A1,Sheet2!A:F,6,FALSE)),"",VLOOKUP(A1,Sheet2!A:F,6,FALSE))

to pickup data from sheet 2 column F etc, or use:

=IF(ISERROR(VLOOKUP(A1,[MyOtherBook.xls]Sheet1!A:G,7,FALSE)),"",VLOOKUP(A1,[MyOtherBook.xls]Sheet1!A:G,7,FALSE))

Select each cell that you put the lookup into and bulk formula drag to the bottom of your data.

When complete, select these lookup columns and Copy, then Paste Special = Values back over themselves.

Hope this helps.

--

Originally Posted by Joeflo

I have 26 worksheets to consolidate. I want to be able to have all worksheet
names on the consolidation sheet. I also want certain information off each
of these sheets to be entered on the consolidation sheet. I have tried the
help, and can get the information from one sheet to the YTD consolidation
sheet, but, when I try to copy the cell, I get the same info there as well.
I don't want to have to enter all of these manually, and there must be a way
that excel does it.

Any help would be appreciated.

Thanks

Joe