Results 1 to 6 of 6

Thread: Copying formulas with complicated pattern

  1. #1

    Exclamation Copying formulas with complicated pattern



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    99
    Articles
    0
    Excel Version
    2013, 2016, O365
    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)
    Last edited by Ron Coderre; 2015-08-19 at 03:14 PM.

  3. #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. #4
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    99
    Articles
    0
    Excel Version
    2013, 2016, O365
    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?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  5. #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. #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
  •