relative "INDIRECT" funtion

[bertusavius]

I am familiar with the INDIRECT function in the sense that it creates a relation to an absolute cell position.
Is it also possible to create a relative relation?

i.e. Is it possible to let a funtion be dependent on a cellPOSITION that is x positions to the right or left of the function cell?

I'd like to be able to drag and drop cells around the sheet and still keep the funtions that relate to these cells untouched in the process, but I dislike the idea that all my functions relate to absolute cell postions because it basically locks the layout of my sheet.

Any help is much appreciated.

 

[RoryA]

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

for example will always refer to the cell one row down and 2 columns to the right.

 

[bertusavius]

Great!

Works like a charm!

Many thx

 

[RoryA]

FYI, you can also use a defined name for this, within a sheet. Select A1. Choose Insert->Name->Define, add a name of your choice (say 'OffsetCell') and enter:
=Sheet1!C2
in the refersto box. Note that both row and column references are relative. Press OK.
Now if you enter =OffsetCell in a cell it will always refer to the cell one row down and two columns to the right.