refer to two cells for location

[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.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)

 

[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?

 

[Fred Smith]

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

Regards,
Fred.

 

[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

 

[yhoy]

Thanks! That works like magic!!!

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[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?

 

[T. Valko]

Try this:

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

Which evaluates to:

='2009 - BS'!A1