refer to two cells for location

Y

yhoy

I have two cells to define a destination cell on differert tab -- one cell
has "tab name", another has "cell location". Struggled a bit to make
Indirect() work under this situation. YOur help is much appreciated!!!
 
E

E.Q.

Could it be that you need the exclamation point? If cell A1 has sheet name
and A2 has cell location try
= Indirect(A1&"!"&A2)
 
Y

yhoy

EQ,

Thanks for help, it is not working yet.

I use if() formula in both A1 and A2, is any incremental effort to formula
below because of it?
 
F

Fred Smith

Post your formula. It will be a lot easier to tell what's wrong with it.

Regards,
Fred.
 
T

T. Valko

If there are spaces or numbers in the sheet name then you have to use single
quotes around the sheet name:

A1 = sheet name = Week 2
B1 = a cell reference = A1

=INDIRECT("'"&A1&"'!"&B1)

Which evaluates to: ='Week 2'!A1
 
Y

yhoy

Thanks! That works like magic!!!

T. Valko said:
If there are spaces or numbers in the sheet name then you have to use single
quotes around the sheet name:

A1 = sheet name = Week 2
B1 = a cell reference = A1

=INDIRECT("'"&A1&"'!"&B1)

Which evaluates to: ='Week 2'!A1
Click to expand...
 
B

bongiman

Is there a way to modify the equation so that

A1 = fist 4 characters of a sheet name = 2009
B1 = a cell ref = A1

so that the formula will for example return A1 on sheet "2009 - BS" given
that no two sheets will have the same first 4 characters and all numbers are
entered as text?
 
Top