# Thread: Discrete cell range when dragging formula through column

1. ## Discrete cell range when dragging formula through column

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. Never, ever, EVER use merged cells.

3. Originally Posted by AliGW
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. =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. Originally Posted by ashtothemac
=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")

#### Posting Permissions

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