Consolidating worksheets

[Guest]

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

 

[Bryan Hessey]

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

Copy the names column from each sheet to a spare worksheet to form
long column.
If you have a main sheet that you want data from other sheets added t
copy it's names to columns A AND B.
If you are starting a brand new consolidation sheet then ignore colum
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 ove
itsself.
Delete columns A and B

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

For each column of data required from other sheets, in row 1 of tha
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 t
the bottom of your data.

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

Hope this helps.

--