Is there a simple way that I can get a cell's current location as text
so I can use it on a formula?


For example I have 5 sheets and I want to get all the sum of the CELL
B2 on all the sheets. I would have a formula like
=Sheet2.B2+Sheet3.B2+Sheet4.B2+Sheet5.B2 <-- this formula would be on
Sheet1 B2. I need to get the "B2" from a formula ie.
Sheet2.(row(),column())? or something to this effect. And is there an
easier way to do this just by copying and pasting? or maybe a script?


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512106

This will give you Sheet2!plus cell address of active cell in Sheet1

=INDIRECT("Sheet2!"&ADDRESS(ROW(),COLUMN()))



--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"cKBoy" wrote in
message ...

Is there a simple way that I can get a cell's current location as text
so I can use it on a formula?


For example I have 5 sheets and I want to get all the sum of the CELL
B2 on all the sheets. I would have a formula like
=Sheet2.B2+Sheet3.B2+Sheet4.B2+Sheet5.B2 <-- this formula would be on
Sheet1 B2. I need to get the "B2" from a formula ie.
Sheet2.(row(),column())? or something to this effect. And is there an
easier way to do this just by copying and pasting? or maybe a script?


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile:
http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512106

Hi thanks for the reply, but I tried your suggestion and I get #NAME?
error. Doesn't Inderect need a letter then a number? row() and
column() both return a number.


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512106

INDIRECT needs a string which it has, replace that with whatever sheet name
you want.
to test it, open a new workbook that defaults to a sheet named Sheet2 and
put the exact formula in Sheet1 and you'll see it will work, you must either
have edited the formula or missed a part when you copied it

this part

ADDRESS(ROW(),COLUMN())

will always return the address of the cell that holds that formula, wrapped
in indirect if put in let's say Sheet1 A2
it will return what's in Sheet2 A2. Normally when using multiple sheets you
can just use

=SUM(first:last!A2)

and it will sum A2 in all sheets that are in-between first and last with
them included, however using indirect you can't use that although you can
use

=INDIRECT("Sheet2!A2")

you can't use

=SUM(INDIRECT("Sheet2:Sheet4!A2")

but as I said

=INDIRECT("Sheet2!"&ADDRESS(ROW(),COLUMN()))

will return what's in Sheet2 and whatever cell address the formula is in
from another sheet

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"cKBoy" wrote in
message ...

Hi thanks for the reply, but I tried your suggestion and I get #NAME?
error. Doesn't Inderect need a letter then a number? row() and
column() both return a number.


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile:
http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512106

Yes, it works! it seems like I just miscopied it or something :) sorry
about that... thank you very much for you help.


--
cKBoy
------------------------------------------------------------------------
cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522
View this thread: http://www.excelforum.com/showthread...hreadid=512106