Forum Discussion
How can I make a formula that is nonconsecutive?
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.
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))
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.
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.
Could you attach a copy of the workbook?
HansVogelaar 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.