ISBLANK & OFFSET Formula

Greetings,

I am using the ISBLANK formula

Formula:

Please Login or Register  to view this content.

and it is still showing values on the Project_Plan tab.

Hoping that this will also fix the rows highlighted with Conditional Formatting where there are no values, as it currently highlight the "empty" rows as well.

Any assistance would be greatly appreciated.

your 2nd arg in the OFFSET function doesn't make sense. you're giving a cell address, but yet on the following page, MS says it is supposed to be a number. num of rows of offset:

https://support.microsoft.com/en-us/...e-b4d906d11b66

Project_Data!E10 contains the text ABC, so it's not blank, so your formula should be evaluating the OFFSET term.

How do you believe conditional formatting isn't working in Project_Plan!C10:P24, Project_Plan!Q10:AW24 and other cells? For instance, each cell in Project_Plan!M10:M24 appears to determine the conditional format for all columns in the same row for Project_Plan!C10:P24. Cell Project_Plan!M20 evaluates correctly as 0, and that means Project_Plan!C20:P20 should have dark grey background.

The formula in Project_Plan!M20 is =IF(ISBLANK(Project_Data!$E20),"",OFFSET(Project_Data!$L20,$B$10,0)), and Project_Data!E20 contains the text Task 9, so it's not blank, so the OFFSET result should be returned, that's the value of cell Project_Data!$L64, which is blank, but Excel converts the value of blank cells to 0 in this type of situation.

Please explain what you're trying to achieve. It isn't completely obvious from your formulas. I can guess that what you need is more ISBLANK calls, specifically for Project_Data!C20 you need to change the formula from =IF(ISBLANK(Project_Data!$E20),"",OFFSET(Project_Data!$E20,$B$10,0)) to

=IF(ISBLANK(Project_Data!$E20),"",IF(ISBLANK(OFFSET(Project_Data!$E20,$B$10,0)),"",OFFSET(Project_Data!$E20,$B$10,0)))

That said, using volatile OFFSET calls in LOTS of formulas can slow down recalculation perceptibly, sometimes annoyingly so. You could use INDEX instead. The immediately previous formula could be changed to

=IF(ISBLANK(Project_Data!$E20),"",IF(ISBLANK(INDEX(Project_Data!$E:$E,ROWS(C$1:C20)+$B$10)),"",INDEX(Project_Data!$E:$E,ROWS(C$1:C20)+$B$10)))

E20 is the same cell as INDEX($E:$E,ROWS(C$1:C20)), and OFFSET(E20,$B$10,0) is the same cell as OFFSET(INDEX($E:$E,ROWS(C$1:C20)),$B$10,0) which is in turn the same cell as INDEX($E:$E,ROWS(C$1:C20)+$B$10). INDEX is nonvolatile, so won't affect recalculation performance as severely as OFFSET can.