indirect function to reference cell on different sheet
 

ok, I have a situation where I have 2 cells on sheet2 that define the
starting and stopping points of a range that I need to reference. I
need to reference them from another sheet (sheet1). I cannot however
get it to work.

for example,
on sheet1 ----- ='Sheet2'!indirect('Sheet2'!a1)
cell a1 on sheet2 contains a cell reference (a2, g3, or whatever) for a
cell on Sheet2, but I need to get the cell in sheet2 that is referenced
by cell a1 in sheet2 back into sheet1

it doesn't like the first 'Sheet2'!, it keeps giving me an error. I
can take it off so that I am left with =indirect('Sheet2'!a1) but then
it will be referencing a cell on sheet1. I am just not sure what the
correct syntax is.


Actually this is just part of a much larger equation...but this is the
part I can't seem to get to work.

thanks in advance for any help


--
Dolemite
------------------------------------------------------------------------
Dolemite's Profile: http://www.excelforum.com/member.php...o&userid=26136
View this thread: http://www.excelforum.com/showthread...hreadid=397194

Are you saying that A1 on Sheet2 contains a reference such as "C3" ?
And you want to reference the value in C3 on Sheet2?

=indirect("'Sheet2'!"&'Sheet2'!a1)


"Dolemite" wrote:


ok, I have a situation where I have 2 cells on sheet2 that define the
starting and stopping points of a range that I need to reference. I
need to reference them from another sheet (sheet1). I cannot however
get it to work.

for example,
on sheet1 ----- ='Sheet2'!indirect('Sheet2'!a1)
cell a1 on sheet2 contains a cell reference (a2, g3, or whatever) for a
cell on Sheet2, but I need to get the cell in sheet2 that is referenced
by cell a1 in sheet2 back into sheet1

it doesn't like the first 'Sheet2'!, it keeps giving me an error. I
can take it off so that I am left with =indirect('Sheet2'!a1) but then
it will be referencing a cell on sheet1. I am just not sure what the
correct syntax is.


Actually this is just part of a much larger equation...but this is the
part I can't seem to get to work.

thanks in advance for any help


--
Dolemite
------------------------------------------------------------------------
Dolemite's Profile: http://www.excelforum.com/member.php...o&userid=26136
View this thread: http://www.excelforum.com/showthread...hreadid=397194

yep that was it...thank you very much...I knew it was going to be
something simple....I just couldn't get it.


--
Dolemite
------------------------------------------------------------------------
Dolemite's Profile: http://www.excelforum.com/member.php...o&userid=26136
View this thread: http://www.excelforum.com/showthread...hreadid=397194