Results 1 to 5 of 5

Thread: Discrete cell range when dragging formula through column

  1. #1
    Neophyte ashtothemac's Avatar
    Join Date
    Jul 2020
    Posts
    4
    Articles
    0
    Excel Version
    Office 365

    Discrete cell range when dragging formula through column



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

    I am trying to drag a formula through a column without the cell range overlapping. Actually the cell range is a merged cell on another sheet. Here is the formula: =IF(COUNTBLANK('Sheet1'!F4:F6)=3, "","Done"). The formula itself works as intended but I want to drag the formula through the column, but the next row will have a range of F5:7 rather than F7:F9.

    Since I merged 3 rows I the range needs to be F4:F6, then F7:F9, F10:F12, etc., but if I drag the function it goes F5:F7, F6:F8, F7:F9

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,618
    Articles
    0
    Excel Version
    Office 365 Subscription
    Never, ever, EVER use merged cells.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte ashtothemac's Avatar
    Join Date
    Jul 2020
    Posts
    4
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by AliGW View Post
    Never, ever, EVER use merged cells.

    Okay. So say I've unmerged the cells. How can I set the function to not overlap the range of 12 cells when I drag through the column?
    A colleague has suggested including INDIRECT within the COUNTBLANK function but I haven't had success with it yet (still working on it though).

  4. #4
    Neophyte ashtothemac's Avatar
    Join Date
    Jul 2020
    Posts
    4
    Articles
    0
    Excel Version
    Office 365
    =IF(COUNTBLANK(Sheet1!F4:F15)=12, "","Done") is the base function I'm working from. I want to be able to then drag the function down so that the next will have the range F16:F27 instead of F5:F16.

  5. #5
    Neophyte ashtothemac's Avatar
    Join Date
    Jul 2020
    Posts
    4
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by ashtothemac View Post
    =IF(COUNTBLANK(Sheet1!F4:F15)=12, "","Done") is the base function I'm working from. I want to be able to then drag the function down so that the next will have the range F16:F27 instead of F5:F16.
    Never mind.
    Got it thanks to jasonb75 on Mr Excel.

    =IF(COUNTBLANK(INDEX(Sheet1!$F:$F,(ROWS(D$3:d3)-1)*12+4):INDEX(Sheet1!$F:$F,(ROWS(D$3:d3)-1)*12+15))=12,"","Done")

    https://www.mrexcel.com/board/thread...pping.1140447/

Tags for this Thread

Posting Permissions

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