Because I have 50 sheets ..
Some add-ons ..
1. Try the sub below to list all the sheetnames in the book at one go:
Press Alt+F11 to go to VBE
Click Insert Module
Copy paste everything within the dotted lines below
into the whitespace on the right
-------begin vba-----
Sub SheetNames()
'Peo Sjoblom in .worksheet.functions Jul '02
Dim wkSht As Worksheet
Range("A1").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub
-------endvba------
Press Alt+Q to get back to Excel
In a *new* sheet, say Sheet1, press Alt+F8
Select "SheetNames" Run
The sheetnames will be listed in A1 down, in this sequence:
1st sheet (leftmost) will be listed in A1,
2nd sheet in A2, and so on
Hidden sheets will also be listed
and will appear after the last (rightmost) sheet
--
2. So, assuming you now have in Sheet1, in A1 downwards,
a tidied up list of all the 50 + sheetnames:
Daniel
Mark
Byran
Hugh
Paul
etc
In the sheet: Summary
[ which contains the earlier VLOOKUP(.. INDIRECT(...) ..) formulas
suggested ]
just put in B1: =OFFSET(Sheet1!$A$1,COLUMNS($A$1:A1)-1,)
and copy B1 across as many cols as you have names to be listed
(this saves you having to manually list all the 50+ sheetnames in B1, C1,
etc)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
|