Sep 26 2021 10:35 AM
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?
Sep 26 2021 10:52 AM
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.
Sep 28 2021 11:42 AM
Sep 28 2021 01:50 PM
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.
Sep 28 2021 05:44 PM
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.
Sep 29 2021 01:29 AM
Could you attach a copy of the workbook?
Sep 29 2021 04:59 AM
@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.
Sep 29 2021 05:20 AM
SolutionYour 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.
Sep 29 2021 05:52 AM
Sep 29 2021 06:38 AM
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.
Sep 29 2021 07:06 AM
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!
Sep 29 2021 05:20 AM
SolutionYour 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.