Help with Address(), Indirect(), Offset

[scott.siegler]

Hello,

I am wondering if it is possible with these three functions to perform the task I am looking to do.

Suppose in cell $A$1 is the string value "$B$10"

In cell B10 is the value 5 and in cell C10 is the value 6.

I can use the indirect function such as indirect(A1) to retrieve the value 5

Is there any way to get the value in C10 by adding a row to the value "$B$10" in cell A1?

I hope this makes sense.

Any help would be greatly appreciated.

Scott

 

[James Ravenswood]

We need to "make" the string "C10" and input that string to INDIRECT(). One way:

=INDIRECT(CHAR(CODE(MID(A1,2,FIND("$",A1,2)-2))+1) & MID(A1,FIND("$",A1,2)+1,256))

 

[James Ravenswood]

Or another:

=INDIRECT(ADDRESS(ROW(INDIRECT(A1)),COLUMN(INDIRECT(A1))+1))