# 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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### 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

1,102,023
Messages
5,484,244
Members
407,435
Latest member
smurfkings247

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...