refer to two cells for location
 

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!!!

refer to two cells for location
 

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

refer to two cells for location
 

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?




"E.Q." wrote:



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

refer to two cells for location
 

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

Regards,
Fred.

"yhoy" wrote in message
...
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?




"E.Q." wrote:



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

refer to two cells for location
 

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

--
Biff
Microsoft Excel MVP


"E.Q." wrote in message
...


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

refer to two cells for location
 

Thanks! That works like magic!!!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"E.Q." wrote in message
...


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

refer to two cells for location
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"yhoy" wrote in message
...
Thanks! That works like magic!!!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"E.Q." wrote in message
...


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

refer to two cells for location
 

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" wrote:

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

--
Biff
Microsoft Excel MVP


"E.Q." wrote in message
...


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

refer to two cells for location
 

Try this:

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

Which evaluates to:

='2009 - BS'!A1

--
Biff
Microsoft Excel MVP


"bongiman" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"E.Q." wrote in message
...


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