software

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(B1lt;gt;quot;quot;,quot;quot;,A1)

In C2 put
=IF(OR(B2lt;gt;quot;quot;,A2=A1),quot;quot;,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)),quot;quot;,VLO OKUP(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)),quot;quot;,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.

--

Joeflo Wrote:
gt; I have 26 worksheets to consolidate. I want to be able to have all
gt; worksheet
gt; names on the consolidation sheet. I also want certain information off
gt; each
gt; of these sheets to be entered on the consolidation sheet. I have tried
gt; the
gt; help, and can get the information from one sheet to the YTD
gt; consolidation
gt; sheet, but, when I try to copy the cell, I get the same info there as
gt; well.
gt; I don't want to have to enter all of these manually, and there must be
gt; a way
gt; that excel does it.
gt;
gt; Any help would be appreciated.
gt;
gt; Thanks
gt;
gt; Joe--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=535223