How to Reference An Offset to the Current Cell in a Worksheet Formula

[CurtisD]

Hi,

I have a worksheet with thousands of rows. Each row contains several cells with formulas that reference other cells on that row.
For example, cell E1 has this formula: =A1+B1. Cell F1 has this formula: =C1+D1.
The same kinds of formulas appear on each row. For example, cell E100 has this formula: =A100+B100.

Is there a way to write a cell's formula so that it uses column offsets from the cell? For example, could the formula for E1
be written as: =OFFSET([currentcell],0,-4)+OFFSET([currentcell],0,-3) where [currentcell] identifies the address of the current cell.

If I could do this, then every formula in a given column would be the same, and would be easier to maintain.

I realize I can drag the fill handle for a formula vertically, and the row references will adjust automatically.
Nevertheless, I would like to know if cell formulas can be written using row and column offsets from the current cell.

Thanks!

 

[FormR]

For example, could the formula for E1
be written as: =OFFSET([currentcell],0,-4)+OFFSET([currentcell],0,-3) where [currentcell] identifies the address of the current cell.

Hi, can't you just replace [currentcell] with the actual current cell reference. i.e. for your F1 example:
=OFFSET(F1,0,-4)+OFFSET(F1,0,-3)

But a better option might be to look at using tables and structured references - google will help if you need more information.

 

[offthelip]

I have done something very similar using the index functions and two "index" references in the rows 2 and 3 of each column
To show you how this works put 1 into E2 and 2 into E3, then put =e2+2 into F2 and E3+2 into F3.
then in E4 put the following equation:

=INDEX($A4:$D4,1,E$2)+INDEX($A4:$D4,1,E$3)

You can then copy this down coolumn E and across to column F and it does what you asked for.