Referencing a sheet by number (position) for its name

[Patrick D. Fox]

Is it possible to determine a sheet name from an Excel function? (my
preference is to do this without a macro or VB)

I have a workbook with 30 sheets - the sheet names are changed frequently -
on the first sheet I'd like to show a list of the names of all of the other
sheets currently?

I'm hoping for some function that allows me to reference the sheet by number
(position in the workbook) to get the name of the that particular sheet.

For example, something along the lines of a formula that looks like this:

=sheet.name[2]
=sheet.name[3]
....and so on...

(again, my preference is to do this without a macro or VB)

Any suggestions?

Patrick

 

[Bernard REY]

Patrick D. Fox wrote :

Is it possible to determine a sheet name from an Excel function? (my
preference is to do this without a macro or VB)

This should suit you:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))

Note that you'll have to save the workbook in order to have it working.

 

[Jonathan Rynd]

I have a workbook with 30 sheets - the sheet names are changed
frequently - on the first sheet I'd like to show a list of the names
of all of the other sheets currently?

Looks like you'd have to use Visual Basic to create a User Defined
Function. Sorry.

Something like the below would do what you want.

public function SheetName(byval i as integer)
SheetName = Worksheets(i).Name
end function

There doesn't seem to be any worksheet function that will tell you the name
of a sheet.

 

[JE McGimpsey]

Looks like you'd have to use Visual Basic to create a User Defined
Function. Sorry.

You don't have to use a UDF.

This will put the sheetname in a cell

=MID(CELL("filename",Sheet2!A1), FIND("]", CELL("filename",
Sheet2!A1))+1,255)

which will return

Sheet2

When first entered, the sheet name needs to be entered as well, but then
when the sheet name is changed, the formula will automatically update.

 

[Bob Greenblatt]

Looks like you'd have to use Visual Basic to create a User Defined
Function. Sorry.

You don't have to use a UDF.

This will put the sheetname in a cell

=MID(CELL("filename",Sheet2!A1), FIND("]", CELL("filename",
Sheet2!A1))+1,255)

which will return

Sheet2

When first entered, the sheet name needs to be entered as well, but then
when the sheet name is changed, the formula will automatically update.

Here's an even sneakier & easier (in my opinion) way to do this. On a sheet,
define a name "WBNamelen", and in the refersto box type:
=LEN(GET.DOCUMENT(88))

Define another name "ThisSheet" and in the refers to box type:
=RIGHT(GET.CELL(32),LEN(GET.CELL(32))-WBNamelen-2)

And then, in any cell where you want the sheet name, simply type =thissheet

To get a list of the worksheets in the workbook in a series of cells, define
another name "WBName" and in the refersto box type:
=GET.WORKBOOK(1)
Then in the first cell of the list type:
=MID(INDEX(WBName,1),WBNamelen+3,255)
In the second cell type:
=MID(INDEX(WBName,2),WBNamelen+3,255)

Etc.

 

[Jonathan Rynd]

=MID(CELL("filename",Sheet2!A1), FIND("]", CELL("filename",
Sheet2!A1))+1,255)

Wow, that's not what the documentation says it does. Thanks!

 

[Jonathan Rynd]

=LEN(GET.DOCUMENT(88))
=RIGHT(GET.CELL(32),LEN(GET.CELL(32))-WBNamelen-2)
=GET.WORKBOOK(1)

Are these GET.* functions documented anywhere?

 

[Bob Greenblatt]

Are these GET.* functions documented anywhere?

Yep! They're old XLM functions documented in the Function Reference manual
for Excel 4. They were greatly expanded in Excel 5 and documented in its
help file. I admit that Microsoft forgot to include them in later versions
of Excel as they have been hoping to get away from the old XLM language, but
it's still there and works fine. In some cases even faster than VBA for
certain functions.