Capture sheet name

[Jes Dideriksen]

When using the referencing or lookup functions, I frequently need to "capture" the name of a sheet.

The function "Sheet" returns only the number of the sheet, even though a description says it returns the name. I need the name.

An example: "VLOOKUP(SHEET();'OK reg'!$79:$84;6;FALSE)". In this case I needed to add a row representing the sheet numbers.

How can I make a sheet name lookup to use as a reference to free me from making a lot of changes, when I add/delete/rearrange sheets?

[#2]

Hi Jes,

Description doesn't say what SHEET() returns sheet name, it always returns the number of the sheet. You may use sheet name as parameter for the function, e.g. =SHEET("Sheet1") returns 1 and =SHEET("Sheet2") returns 2.

If you'd like your formula works with one sheet or another you may manually put sheet name in any cell, let say A1, at combine it with your formula using INDIRECT().

[#3]

Hi Sergei

Of course, you're right. there is nothing wrong with the example.

Anyone else that know of a function that reveals the sheet name?

[#4 · Accepted solution]

That's only VBA or parse result of the

=CELL("filename",<cell>)

[#5]

Thanks. I'll try that.