Worksheet Index in a cell function

[Jonathan]

Hi

I want to create a cell formula that refers to data on another sheet.
My problem is that I don't know the worksheet name, only its index.

E.g I couldn't do =Data!$A$1 becuase I don't know the sheet is called
data
I need (something like) =WORKSHEETS(3)!$A$1

Anyone have an idea that will point me in the right direction ??

Many thanks
John

 

[Harald Staff]

Hi John

There's no built-in function for this. Which is too bad, it would be very
useful. But try John Walkenbach's solution at
http://www.j-walk.com/ss/excel/tips/tip63.htm

It's a VBA user-defined function. See
http://www.mvps.org/dmcritchie/excel/getstarted.htm if you're unfamiliar
with what they are and where they go.

 

[JRH]

Thanks Harald

I suspected as much!
I'll need several thousand of these formulae so UDFs are out of of the
question I'm afraid (far too slow!)
I guess the next best thing is to write VBA to construct the cell formulae
and insert them in automatically as the workbook opens.

Many thanks
John