How to reference a sheet by number not name in formula?

[GuyGadois]

I have a situation where I want to reference a worksheet by sheet number and not by sheet name because the sheet name changes based on a user input (sheet name will never be standard).

Typically I could use the following formula to get the value in cell B10 on sheet called B10:
='BobSmith'!B10

The problem is that sheet can be changed to anything by the user so I want to refer to it by its sheet number, which in this case is Sheet5.

Can this be done?

Cheers,

Guy

 

[mumps]

Sorry..misposted.

 

[Norie]

Guy

You can't use a sheets codename directly in a formula.

 

[Fluff]

To the best of my knowledge, the sheet codename & sheet index only exists in VBA & therefore cannot be accessed by an xl formula

 

[Jonmo1]

The good news is, that once you have the formula entered, the sheet can be renamed all you want, and the formula will automatically correct itself.

This formula
='BobSmith'!B10

Rename that BobSmith to FredFlintstone

The formula automatically changes to
='FredFlintstone'!B10

 

[mopp1]

When a sheet name is changed, Excel will automatically change the references to it elsewhere in the workbook. Therefore a user changing a sheet name will not disable the formulas using that sheet's name.