You could have a table that lists the sheet number in one column & the sheet
name in the other, then use a VLOOKUP() to supply the sheet name to Bob's
INDIRECT() function. All you'd have to do then is maintain the table of
sheet names


"Wes" wrote:

To bad there isn't an easy solution, since the problem I have is that they
all have sheet names that have been changed, and may change again. That is
what led me to this problem in the first place.

Thanks for your help though.

"Bob Phillips" wrote:

No as easily.

You could use

=INDIRECT("Sheet"&A1&"!C5")

as an example, as long as they all follow the Sheet1, Sheet2, ... format.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" wrote in message
...
Sorry I guess I didn't word my question clearly. I'm wanting to do this
as a
worksheet function. Is that possible? I was hoping to make this
spreadsheet
entirely without VBA.

Thanks.

"Bob Phillips" wrote:

Worksheets(Range("A1").Value).Range("somecellref") .Value

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Wes" wrote in message
...
Just wondering if there is a way to reference a sheet without using
its
name.
I know in VBA you can reference the first, second, third etc. sheet
or
use
their names.

I want to use a formula that references a certain sheet dependent on a
different cell value. For example: if cell a1 value is 3, then my
formula
will refernce a cell on the third sheet. if cell a1 is 2, then the
formula
would reference the same cell but on the second sheet.

Thanks in advance for your help.