How to increase cell value by 1 every 4 cells

[TripletDad]

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!

 

[Jonmo1]

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]

try this

Excel Workbook
	A
1	Tom
2	Tom
3	Tom
4	Tom
5	****
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A1	=IF(COUNTIF(INDIRECT("MAIN!A"&1),A1)<4,INDIRECT("MAIN!A"&CEILING(ROW(A1)/4,1)))

copy the formula down

 

[Oaktree]

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]

=indirect("'Main'!A"&quotient(row()-1,4)+1)

(if you install the analysis toolpak)

 

[TripletDad]

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!