Variable Cell References

[Matt]

Wondering if someone can help

How do you right a variable cell reference, i.e. you want
either the column or row (or both) to change dependent on
the result of another formula?

Any help greatly appreciated.

Matt

 

[Harlan Grove]

How do you right a variable cell reference, i.e. you want
either the column or row (or both) to change dependent on
the result of another formula?

Several ways. The most efficent is

=OFFSET(BaseAddress,RowOffset,ColumnOffset)

For example,

=OFFSET($A$1,5-1,6-1)

refers to F5. Beware suggestions to use

=INDIRECT(ADDRESS(R,C))

Pointless & wasteful. You could use

=INDIRECT("R"&R&"C"&C,0)

to the same effect. I prefer the OFFSET approach because it's more flexible.

 

[TKT-Tang]

Look at ADDRESS and INDIRECT on the Help File. It may help, perhaps.

Regards.