relative "INDIRECT" funtion

bertusavius

Board Regular
Joined
Feb 28, 2008
Messages
82
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Code:
=INDIRECT(ADDRESS(ROW()+1,COLUMN()+2))
for example will always refer to the cell one row down and 2 columns to the right.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top