Sheet Names and Cell References
 

Hi,

I am using the formula below and others like it to extract certain data from
a single workbook into several different workbooks. I have multiple sets of
information that are all on different sheets, but the workbook name and cell
location remain the same.

Instead of changing the "345KV CANIFF STEPHENS" portion of my formula 20
times per new workbook, I would like to be able to use a formula that would
get the sheet name from cell A1. How can I do this?

='[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]345KV CANIFF STEPHENS'!$N5

I would also like to know if there is a way to list all of the sheet names
on one sheet.

Thanks,

Reed

For your second question, try this macro to list all
worksheets:

Sub ListWSNames()
Dim sh As Worksheet
Dim i As Long

With ActiveWorkbook
.Worksheets(1).Select
Set sh = .Worksheets.Add
End With

With sh
For i = 2 To ActiveWorkbook.Worksheets.Count
.Cells(i, "A").Value = Worksheets(i).Name
Next i
.Cells(1, "A").Value = "Sheet Names (excl. this one)"
End With

Range("A:A").EntireColumn.AutoFit

End Sub

----
HTH
Jason
Atlanta, GA

-----Original Message-----
Hi,

I am using the formula below and others like it to
extract certain data from
a single workbook into several different workbooks. I
have multiple sets of
information that are all on different sheets, but the
workbook name and cell
location remain the same.

Instead of changing the "345KV CANIFF STEPHENS" portion
of my formula 20
times per new workbook, I would like to be able to use a
formula that would
get the sheet name from cell A1. How can I do this?

='[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]345KV
CANIFF STEPHENS'!$N5

I would also like to know if there is a way to list all
of the sheet names
on one sheet.

Thanks,

Reed


.