Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula for sheet & cell reference | Excel Worksheet Functions | |||
replacing just the sheet names within formulas | Excel Worksheet Functions | |||
Cell reference problems with Summary sheet | Excel Worksheet Functions | |||
Unable to set the active cell from VBA | New Users to Excel | |||
Worksheet copy problem - local names | Excel Discussion (Misc queries) |