# increment cell reference

#### leecavturbo

##### Well-known Member
in a cell H3 i have ref to =C3

i need H4 to ref cell =B3

i need H5 to ref cell =C4

and repeat down many rows . if i copy down excel doesn't work out i need the pattern to repeat. how can i simply copy down?

i.e then in H6 need =B4
H7 =C5
H8 =B5
and so on

TIA

Last edited:

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Repush

##### Board Regular
try INDIRECT in combination with ROW

#### AlanY

##### Well-known Member
try this in H3, and copy down

Code:
``=OFFSET(\$A\$1,ROUND((ROW(A3)+2)/2,0)-1,IF(MOD(ROW(A3),2)=0,1,2))``

#### leecavturbo

##### Well-known Member
try this in H3, and copy down

Code:
``=OFFSET(\$A\$1,ROUND((ROW(A3)+2)/2,0)-1,IF(MOD(ROW(A3),2)=0,1,2))``
worked perfectly much thanks

#### AlanY

##### Well-known Member
you're welcome

the formula can be simplified as

Code:
``=OFFSET(\$A\$1,ROUND((ROW(A3))/2,0),IF(MOD(ROW(A3),2)=0,1,2))``
with same results

Last edited:

#### leecavturbo

##### Well-known Member
care to roughly explain how it works?
i may be able to adapt it in future

#### AlanY

##### Well-known Member
care to roughly explain how it works?
i may be able to adapt it in future
Code:
``=OFFSET(\$A\$1,ROUND((ROW(A3))/2,0),IF(MOD(ROW(A3),2)=0,1,2))``
the offset() function is refer to a range (a cell in your requirement) from a reference cell (\$A\$1) by offsetting it with rows and columns.

e.g. offset(A1,1,1) will offset A1 by 1 row and 1 column that gives B2 etc
from then on is just to find the patterns so that it can copy down from H3