Reference another sheet without knowing it's name

[duffry]

Is there a means to reference other sheets without knowing their name within a formula.
I know it can be done in VBA with sheets(1) etc and looking for something much the same within a formula (without a UDF).
Sheet order isn't a problem, just their names.

TIA

[Jonmo1]

Not in a formula, at least not without VBA.

A formula can't refer to the Sheet Index number like VBA can with Sheets(1)

[duffry]

I feared as much.
Thanks for the speedy reply.

[Jonmo1]

You're welcome..

Just curious why this is important?
Are you just worried about sheets getting renamed?
Formulas will automatically adjust themselves when a sheet is renamed.

[duffry]

I have a grid of colourd blocks that forms a pseudo-chart.
I have been able to jigger the formula in the cell enough that I can use the same formula in each cell (with automatic relative adjustment) for each hour in each day but the data for each week is on a different sheet. The sheet will also be copied and changed for a new month. The renaming should, as you say, be fine to adjust the formulas once set up. I was just hoping to not have to rejig them to get them set up.

Not a big issue and honestly I've almost worked around it anyway. It was more curiosity that led me to post.
:o)

[Tony Valko]

Just for the heck of it, you may find this interesting:

http://www.excelforum.com/tips-and-t...a-formula.html

You could adapt that technique to generate the sheet name based on the sheet number.

Technically, it's not VBA but would still require the file to be saved as a macro enabled file in Excel 2007 and later.

[duffry]

Like it, thanks.

[Tony Valko]

You're welcome!