How can I make a formula that is nonconsecutive?

[Budman361530]

I have a sheet that pulls data off another sheet, but the reference cells are 7 apart.   Example:  =Sheet 1!7.  then Sheet 1!14, Sheet 1!21… etc.  

I need to make like 100 cells, and dragging the cells just repeats that group.  Is this possible?

[#2]

@Budman361530 

I assume that you mean something like ='Sheet 1'!A7 etc.

Let's say the first formula will be in a cell in row 1.

Enter the following formula in that cell:

=INDEX('Sheet 1'!A:A, 7*ROW())

Fill down as far as you need.

If the first formula will be in row 2 instead of row 1, use

=INDEX('Sheet 1'!A:A, 7*ROW()-7)

etc.

[#3]

Hey Hans,
Thank you for responding. However, It’s not working for me. Can you please walk me through this? Here is my actual formula.
=INDEX('SUB TOTAL'!K:K,7*ROW()-7)
The information starts on Row 7 of the K column. I understand the K:K part. That basically says, the entire column. I don’t understand the “ 7*ROW()-7) “ part, and how that equals the second row. How would I get it to see the 7th row? Then the 14th row, 21st, etc…
Also, I don’t think this will matter, but I am not sure? The cells I am trying to reference in the K columns (7), (14)… etc. are all a formula in themselves. It’s not a clean cell… meaning a number only. Can that effect my formula above?
Example…. Cell K7 on the Sub Total page is actually this…. =IF(SUM(J6:J7)>40,40,SUM(J6:J7))

[#4]

@Budman361530 

It doesn't matter whether the cell referred to contains a fixed value or a formula.

It all depends which row contains the first formula.

If the first formula is in row 1. use 7*ROW() for in row 1, 7*ROW() = 7*1 = 7

If the first formula is in row 2, use 7*ROW()-7 for in row 2, 7*ROW()-7 = 7*2-7 = 14-7 = 7

If the first formula is in row 3, use 7*ROW()-14 for in row 3, 7*ROW()-14 = 7*3-14 = 21-14 =7

Etc.

In each of these situations, the first formula refers to row 7 on the SUB TOTAL sheet.

[#5]

@Hans Vogelaar 

Ok… the way you explained it, I get it.  Thank you!  However, it isn’t working, and I just want to make sure we are on the same page. 

This is my Payroll Summary page.

The cell where the arrow is pointing should populate information from the following page below, but every seventh cell in column K.

This is the formula I am using:  =INDEX('SUB TOTAL'!K:K,7*ROW()-42)

The other issue I am having, is when I try to drag the box (my formula) it does not auto populate.  It stays at 42 vs, 49, 56… etc.

[#6]

@Budman361530 

Could you attach a copy of the workbook?

[#7]

@Hans Vogelaar Just uploaded a copy of my file.  See if you can see it.   On the "Payroll Summary" tab, in the column "Standard Time" I put the formula we have been talking about in row G15.  I then drug it down to show you want is going on.  The column "Overtime" next to it uses the original or existing formula.  I am trying to make this sheet/workbook expandable.   

[#8 · Accepted solution]

@Budman361530 

Your first formula is in G15. In G15, ROW() = 15, so 7*ROW() = 105. You want to return the value from row 7 on the SUBTOTAL sheet, so you have to subtract 105-7 = 98:

=INDEX(SUBTOTAL!K:K,7*ROW()-98)

See the attached version.

[#9]

Ok... so where I was confused was that number was referencing the “subtotal page” but it is referencing the row on the payroll summary page. So the next one would be 105, 112.. etc. ? But how do I get the formula to know on the second row… row (-105) to refence (K14 and not K7) on the “subtotal page”, and or why isn’t it auto populating when I drag it down?

[#10]

@Budman361530 

On row 16, 7*ROW()-98 = 7*16-98 = 112-98 = 14

On row 17, 7*ROW()-98 = 7&17-98 = 119-98 = 21

Etc.

[#11]

@Hans Vogelaar 

I found the problem!  Your explanation was awesome.  I cannot thank you enough!!!! You getting me to understand how the formula works, step by step got me to realize something else was not calculating correctly.  There was an error on the Overhead page that was not allowing the data to come over to the subtotal sheet.  Once I saw what you were saying I thought to myself.... I need to look at my data coming in, and I found the problem!  Again, THANK YOU THANK YOU THANK YOU!  Have an awesome day!