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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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
8,053
Office Version
  1. 365
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,029
Messages
5,856,933
Members
431,840
Latest member
simplysoph

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top