Reference to a worksheet name in a cell on another sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Howard Kaikow - 12 Apr 2008 20:06 GMT

Is there a way to use a built-in worksheet function, or defined name, to
refer to a worksheet name?

I'd like to fill a cell with the name of a worksheet, yet have the value in
that cell
automatically change if the worksheet name changes.

Reply to this Message

Shane Devenshire - 12 Apr 2008 20:18 GMT

Hi Howard,

Try something like this:

=MID(CELL("filename",Sheet2!C11),FIND("]",CELL("filename",Sheet2!C11))+1,100)

Where Sheet2!C11 is any cell reference on the sheet whose name you want.
For the active sheet, the one with the formula you can leave out the
Sheet2!C11.

Cheers,
Shane Devenshire
Microsoft Excel VBA

> Is there a way to use a built-in worksheet function, or defined name, to
> refer to a worksheet name?
[quoted text clipped - 3 lines]
> that cell
> automatically change if the worksheet name changes.

Reply to this Message

Howard Kaikow - 12 Apr 2008 21:11 GMT

> Hi Howard,
>
> Try something like this:

=MID(CELL("filename",Sheet2!C11),FIND("]",CELL("filename",Sheet2!C11))+1,100
)

> Where Sheet2!C11 is any cell reference on the sheet whose name you want.
> For the active sheet, the one with the formula you can leave out the
> Sheet2!C11.

Thanx.

Wonder why "sheetname" is not provided as an Info_type?

Reply to this Message

Shane Devenshire - 13 Apr 2008 05:13 GMT

Hi Howard,

I've suggested that to Microsoft in the past but nothing has come of it.
"Book", "Path" and "Drive" might also be nice. And there are others.

Cheers,
Shane

>> Hi Howard,
>>
[quoted text clipped - 10 lines]
>
> Wonder why "sheetname" is not provided as an Info_type?

Reply to this Message

Rate this thread: