how to reference a cell one up and one back

[merlin]

I'm trying to do a roster pattern which involves each person doing the same
but one day out of phase with the previous person.

Basically, I need to fill a cell with the value in the cell one columnback
and one row up (or sometimes 3 up and 3 back). In this way I can roster all
19 people without retyping each line.

I'm not quite sure how to manipulate cell references to do this. Please
could someone advice me?

many thanks IA

 

[Gord Dibben]

In B2 enter =OFFSET(B2,-1,-1) to return value from A1

Basics are =OFFSET(cellref,rowindex,columnindex)

Negative indices are up and left................positive indices are down
and right.

See help on the OFFSET function for more info and examples.


Gord Dibben MS Excel MVP