Brain Teaser -- Cell References

[Tom]

I may have a brain teaser here...

I have hundreds of cell references (as an actual value)
in a worksheet. For instance, cell A2 contains "$K$16",
cell A3 contains "$B$90", etc.

I need to point via this cell address value to an another
column/row and find out what the value is in
- column B
- of the row number listed in the cell reference

Example - Steps:
a. Sheet2 contains "$K$16" in cell A2
b. Retrieve value of cell $B$16 from Sheet1
c. Place value of Sheet1!$B$16 into Sheet2!$B$2


Anyone having some suggestions or solutions?


Thanks,
Tom

 

[Ken Wright]

Try this in cell B2 on sheet 2, it should copy across OK as well.

=OFFSET(Sheet1!$A$1,ROW(INDIRECT($A2))-1,COLUMN()-1)

 

[Paul Corrado]

Tom,

In Cell Sheet2!B2 use

=OFFSET(INDIRECT("sheet1!"&A3),0,-9) to return the the value in sheet1!B16
based on the reference to K16

If however $K$16 was a type and should have been $B$16 then this should
work.

=INDIRECT("sheet1!"&A2)

PC

 

[Ken Wright]

In fact, if it is only ever Column B you would want to look at then the following will probably do
it as well:-

=INDIRECT("Sheet1!B"&ROW(INDIRECT($A2)))

I had assumed you wanted the column address from whatever column your formula was in, and
therefore potentially wanted the data from C16, D16 etc as well.

 

[Tom Ogilvy]

In B2 of Sheet2
=INDIRECT("Sheet1!B"&ROW(INDIRECT(A2)))

then drag fill down column B.

Regards,
Tom Ogilvy

 

[Tom]

Thanks, Paul, this works great!

Tom

-----Original Message-----
Tom,

In Cell Sheet2!B2 use

=OFFSET(INDIRECT("sheet1!"&A3),0,-9) to return the the value in sheet1!B16
based on the reference to K16

If however $K$16 was a type and should have been $B$16 then this should
work.

=INDIRECT("sheet1!"&A2)

PC






.

 

[Tom]

Ken:

As always, you have come through with yet another great
solution!

Thanks for sharing your knowledge!!!!

Tom