increment cell reference

[leecavturbo]

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

 

[Repush]

try INDIRECT in combination with ROW
hit F1 for more info

 

[AlanY]

try this in H3, and copy down

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

 

[leecavturbo]

try this in H3, and copy down

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

worked perfectly much thanks

 

[AlanY]

you're welcome

the formula can be simplified as

=OFFSET($A$1,ROUND((ROW(A3))/2,0),IF(MOD(ROW(A3),2)=0,1,2))

with same results

 

[leecavturbo]

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

 

[AlanY]

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

=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