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.
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.
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.
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!
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.
- 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?