Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 19th 05, 11:06 PM
Reed
 
Posts: n/a
Default 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   Report Post  
Old January 19th 05, 11:17 PM
Jason Morin
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need formula for sheet & cell reference MPH Excel Worksheet Functions 0 January 16th 05 04:39 PM
replacing just the sheet names within formulas Jeanne Criez Excel Worksheet Functions 1 January 4th 05 07:27 PM
Cell reference problems with Summary sheet McIntyre Excel Worksheet Functions 3 December 30th 04 06:29 PM
Unable to set the active cell from VBA HMS New Users to Excel 1 December 7th 04 10:56 PM
Worksheet copy problem - local names Jack Sheet Excel Discussion (Misc queries) 2 December 2nd 04 11:02 AM


All times are GMT +1. The time now is 02:02 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017