Forum Discussion
How can I make a formula that is nonconsecutive?
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?
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.
10 Replies
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.
- 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))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.
