# Thread: Copying formulas with complicated pattern

1. ## Copying formulas with complicated pattern

Hello,
I am new to posting on a forum...I have run out of steam trying to produce a new worksheet. There is a Daily tab and a Weekly tab. The weekly references the daily information. The formula I created references every 22nd row, but when I click and drag the formulas, it does not recognize the pattern.

Formula:
=INDEX(DAILY!\$D\$1:\$D\$458,(ROWS(\$F\$5:F5)-1)*\$Q\$3+\$Q\$2)
=INDEX(DAILY!\$G\$1:\$G\$458,(ROWS(\$F\$6:F6)-1)*\$Q\$3+\$Q\$2)
=INDEX(DAILY!\$J\$1:\$J\$458,(ROWS(\$F\$7:F7)-1)*\$Q\$3+\$Q\$2)
Q3 is the increment by 22 and Q2 is the starting line number 18

It pulls the data from the daily sheet properly for the first week, but when I click and drag down (to create each week of the year) F5 gets changed to the actual cell the formula is in. If I change =INDEX(DAILY!\$D\$1:\$D\$458,(ROWS(\$F\$5:F5)-1)*\$Q\$3+\$Q\$2) to =INDEX(DAILY!\$D\$1:\$D\$458,(ROWS(\$F\$5:F6)-1)*\$Q\$3+\$Q\$2) the next weeks data is properly populated. But when I click and drag it automatically gets changed to =INDEX(DAILY!\$D\$1:\$D\$458,(ROWS(\$F\$5:F36)-1)*\$Q\$3+\$Q\$2) and I get an error message.

I can attach a copy if need be since this is difficult to explain in writing.

Thank you for any input!

2. It sounds like you have hidden rows that you are crossing over when dragging.
What cells contain the formulas before and after dragging and what are tne new references?

-Ron Coderre
Microsoft MVP (2006-2015)

3. Thank you for responding to my post. There are no hidden rows.
Cell that contains the information before is F5 and after is F36.

4. You'll need to clarify what you're doing.
If the original cell is F5 and the next cell is F36, then you didn't drag the formula down to F36 without also filling in F6:F35.
So, how is the formula getting from F5 to F36? Are you copy/pasting?
If Yes...are you pasting the original formula into every 31 rows?

5. I'm finding it difficult to write this clearly, so I have attached a copy of my work. I am trying to drag the week down to make each week for the rest of the year. Ty

6. Thank you for any responses to my post. I figured out the solution. I'm using match and index which now enables me to click and drag to create each week for the rest of the year!!! YAY

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•