Simple way to get cell location as text "A2"

[cKBoy]

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


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

 

[Peo Sjoblom]

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]

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() an
column() both return a number

 

[Peo Sjoblom]

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]

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