increment cell reference

leecavturbo

Well-known Member
Joined
Jan 4, 2008
Messages
622
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:

Some videos you may like

Excel Facts

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

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
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))
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
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:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,369
Messages
5,486,449
Members
407,547
Latest member
Sankarasrinivas

This Week's Hot Topics

Top