Discrete cell range when dragging formula through column

ashtothemac

New member
Joined
Jul 17, 2020
Messages
4
Reaction score
0
Points
0
Excel Version(s)
Office 365
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
 
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).
 
=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.
 
Back
Top