accessing an address stored in another cell

[Guest]

Is it possible to access an address stored in another cell? For example:

G100 = "test"

A100 = +G100

Now, can I get any information about the address stored in A100, which is
G100?

In C100, I would like to do something like =ROW(address stored in A100).

 

[Guest]

look at the indirect function

 

[Guest]

Thanks, but Indirect wants a textual cell address and that's not what resides
in my target cell (A100).

 

[Mangesh Yadav]

Use the udf:

The Code for GetFormula
Function GetFormula(Cell as Range) as String
GetFormula = Cell.Formula
End Function

Source:
http://www.mvps.org/dmcritchie/excel/formula.htm


Mangesh

Thanks, but Indirect wants a textual cell address and that's not what resides
in my target cell (A100).

A100).

 

[Biff]

Hi!

In A100 enter G100, don't use the plus sign. You don't need to use plus
signs like that in Excel, anyhow.

So, if you have in cells:

G100 = test
A100 = G100

=INDIRECT(A100) will return test.

Biff

 

[Guest]

Thank you very much! uh -- one little problem. This is the first time I've
used the VB Editor. I pasted in

Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
End Function

and still don't get anything but an error, when I do something like

=GetFormula(B1534)

 

[Mangesh Yadav]

In C100, enter:

=1*MID((MID(GetFormula(A100),2,255)),MATCH(FALSE,ISERROR(1*MID((MID(GetFormu
la(A100),2,255)),ROW(1:10),1)),0),255)

confrim with shift control enter

Use the UDF to GetFormula as shown in my earlier post. The formula above is
given in
http://www.emailoffice.com/excel/arrays-bobumlas.html


Mangesh



A100).

 

[Guest]

Okay, thanks again for the great help. For some reason, both formulas give
me an error. Maybe it has something to do with my version being 97.

 

[Mangesh Yadav]

In the VBE, go to Insert > Module
Then paste the code in this module.

Mangesh

 

[Mangesh Yadav]

You have probably pasted the code in the wrong module. As I said, put in in
a standard module by going to Insert > Module. and then pasting the code.

Mangesh

 

[Guest]

Got it! Thanks again for the top notch comments.

 

[Mangesh Yadav]

Glad its working. Thanks for the feedback.

Mangesh