How to increase cell value by 1 every 4 cells

TripletDad

Board Regular
Joined
Oct 19, 2010
Messages
121
Excellers! I'm new at this ... this is only my second question. Hopefully somebody can help.

I'm trying to create a formula or find a way to do the following:

In Column A, I need it to reference the same cell on a different sheet four times, then reference the next cell. Then repeat. So it would look something like this:

='MAIN'!A1
='MAIN'!A1
='MAIN'!A1
='MAIN'!A1
='MAIN'!A2
='MAIN'!A2
='MAIN'!A2
='MAIN'!A2
='MAIN'!A2
='MAIN'!A3
='MAIN'!A3
='MAIN'!A3
='MAIN'!A3
...etc

This needs to be carried out for several hundred rows.... what is the best way to do this?

Thanks guys!
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

=INDEX('MAIN'!A$1:A$1000,INT((ROWS(A$1:A1)-1)/4)+1)

4 = Interval (every 4 rows)
1 = Row to begin with.

Do not change the ROWS(A$1:A1) - it has nothing to do with your data.

Hope that helps.
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
try this
Excel Workbook
A
1Tom
2Tom
3Tom
4Tom
5****
Sheet1
Excel 2003
Cell Formulas
RangeFormula
A1=IF(COUNTIF(INDIRECT("MAIN!A"&1),A1)<4,INDIRECT("MAIN!A"&CEILING(ROW(A1)/4,1)))

copy the formula down
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
Another option would be to use a helper column:

C1:C4 all = 1
C5 = C1+1, copied down
A1 = INDIRECT("'Main'!A"&C1) copied down
 

Asator

Board Regular
Joined
Apr 5, 2010
Messages
186
=indirect("'Main'!A"&quotient(row()-1,4)+1)

(if you install the analysis toolpak)
 

TripletDad

Board Regular
Joined
Oct 19, 2010
Messages
121
Thanks for all the help guys! No wonder I couldn't make it happen - these formulas are different than I've ever used before...

I'll work on this asap.

Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top