Excel - reference a worksheet name?

[Not Mike]

Does anyone know if it's possible to reference a worksheet name in Excel?

For instance, if I were setting up a workbook with 20 sheets, where each sheet represented 20 different divisions of a company, and I wanted cell A1 to contain that division name (which would correspond to the worksheet name), can I do that with a reference?? Something similar to =cell("filename"), maybe?

Thanks....

[General Kenobi (ret.)]

=indirect("SheetName!A31")

[Not Mike]

On 2002-02-04 10:39, Obi-Wan Kenobi wrote:
=indirect("SheetName!A31")


Obi-Wan, that allows me to pull a specific value from the sheet, but what I want to pull in the actual name of the worksheet, if it's possible, which I'm not sure it is..... thanks for the help, though...

[NoName]

I don't know if there is a way to do exactly what you want, but you can always write a macro and run it at appropriate times

Sub blah
for each w in worksheets
w.cells(1,1)=w.name
next
end sub

[Not Mike]

NoName, thanks a lot.... given that you can write a macro to do it, it must be possible with a function, but at least I can use this when I need to...

[Sancho]

=CELL("FILENAME",A1) will give you the sheet name as well as the directory and filename.

[Patience]

note that you use the actual word filename, don't put the filename there.

also note "address" instead of filename gives less of the path

[Oracle]

And, if NM wants just the sheet name, he's going to have to do some "editing" of the result from CELL("filename"), since it will also show the entire path to his spreadsheet. It can be done, using the MID function, but it's not very pretty.

[Not Mike]

Thanks again.... hopefully in the next version of Excel, there will be a cell("Sheetname")...

[CaptainCavy]

=right(cell("filename",A1),len(cell("filename",A1))-find("]",cell("filename",A1)))

[edited to add cell references to cell(); otherwise Excel doesn't refresh correctly if you have a multi-tabbed worksheet]

<font size=-1>[ This Message was edited by: CaptainCavy on 2002-02-05 16:06 ]</font>

[Dr T Non-Fan]

Excellent, CC.

I recall reading an old Lotus magazine that had puzzles to solve. That was fun, until I saw the light.

[General Kenobi (ret.)]

Non-Mike: Don't hold your breath. At least through Office 2K, there was no direct way to include the full filename with path in the footer (though there's a relatively easy workaround). The features that get added are the ones that look good in ads, not the ones that we actually need.

“Did they fix this in Office XP?” he asked, expecting the answer, “No!”

[Minerva]

I didn't read the original post to asking about footers, but rather trying to reference sheets, still ---

Even though I am almost Excel macro-illiterate, I have set up a macro to write the footer, indcluding the path name, my initials and the date/time the particular sheet is being updated. I've linked this macro to both a button and to a custom menu item. If I can do it - anyone can.

Sub BasicFooter()
'
' BasicFooter Macro
'
'

With ActiveSheet.PageSetup
range1 = ActiveWorkbook.Path
range2 = ActiveWorkbook.Name
range3 = "" & range1 & "" & range2 & " [&A]"
range4 = Date$
range5 = Time$
.LeftFooter = range3
.CenterFooter = ""
.RightFooter = "My initials or other text - " & range4 & " " & range5
End With
End Sub

[General Kenobi (ret.)]

That wasn't the original question--it was an aside on my part. But there's an even better way to do it--go to your default workbook and add your code to the Workbook_BeforePrint event.

[Minerva]

Still on the tangent -

Obi-Wan - if you do it that way, can you get last saved date (like you can in Word), or will it be print date? (The reason I set up the macro to start with was to keep the date/time static - otherwise I can't always tell whether a spreadsheet has changed.)

[CaptainCavy]

Minerva...

The VBA function FileDateTime() will return the date and time at which the file was either created or last saved. See help while in the Visual Basic Editor for more info and an example.

[Oh Yeah]

You can write a macro that looks like this:
(Say you have 15 worksheets)

i=1
Do While i < 16
Sheets(i).Select
Range("A1").Select
ActiveCell.FormulaR1C1 = ActiveSheet.Name
i = i + 1
Loop

[Oh Yeah]

Sorry. I didn't read the entire thread. And I was proud of my little macro. :oops:

[General Kenobi (ret.)]

FWIW, Worksheets.Count will give you the number of sheets in the active workbook, so you can generalize your macro a little more.

[wtt]

=indirect("SheetName!A31")
It is the way to name that.