# Thread: increment cell reference Thanks: 0 Likes:  2 Post #5311999 (1)Post #5311997 (1)

1. ## increment cell reference

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

2. ## Re: increment cell reference

try INDIRECT in combination with ROW

3. ## Re: increment cell reference

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))`

4. ## Re: increment cell reference

Originally Posted by AlanY
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

5. ## Re: increment cell reference

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

6. ## Re: increment cell reference

care to roughly explain how it works?
i may be able to adapt it in future

7. ## Re: increment cell reference

Originally Posted by leecavturbo
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