Thread: increment cell reference Thanks: 0 Likes:  2 Post #5311997 (1)Post #5311999 (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  Reply With Quote

2. Re: increment cell reference

try INDIRECT in combination with ROW  Reply With Quote

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))  Reply With Quote

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  Reply With Quote

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  Reply With Quote

6. Re: increment cell reference

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

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  Reply With Quote

User Tag List

Tags for this Thread

=c5, cell, copy, ref, repeat  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•