Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Not is blank formula not working

Copper Contributor

I am trying to conditionally format a spreadsheet currently using the NOT(ISBLANK) formula.

 

I have used the same formula reading 

=NOT(ISBLANK($AN18:$AN26))

and this is working fine. The new formula reads 

=NOT(ISBLANK($F12:$K12))

and this is not functioning on the spreadsheet properly??

 

They are on two different spreadsheets the only difference is before it was reading a column and now it is needing to read a row.

Please help!

9 Replies

@MRobinson0297 

Can you explain in words which range you want to format and what the rule is intended to do?

So I need the formula to read the row below it and make the cell white if there is something in the corresponding below cell.

For example if this was on cell A1 I want it to read B1.
A1 is currently green.
If there is a date in B1 I want A1 to go white.

@MRobinson0297 

Let's say you want to apply this to F11:K11.

Select this range. F11 should be the active cell in the selection.

Create a conditional formatting rule of type "Use a formula to determine which cells to format", with formula

 

=F12<>""

 

This is the condition for the active cell F11. Excel will automatically adjust it for the other cells in the selection.

Will this work for every cell to read the corresponding one below it or will they all just read the F11 cell?

@MRobinson0297 

F11 will look at F12, G11 will look at G12, etc.

I must be doing something wrong.

This is the formula I am using on row 55 but it is registering the cell underneath it as well as F56 being a overriding cell that clears the entire row.

=F56<>NOT(ISBLANK($F$56:$K$56))

@MRobinson0297 

Please explain in words what exactly you want to accomplish

@Hans Vogelaar 

So the spreadsheet is used to track dates for projects. I currently have it set up so that conditional formatting reads the date and changes it red/green/yellow depending on how close it is.
Underneath that I have a revised date box.

 

The function I need the formula to perform is that when the revised date box has the date added I want the target date box above it to go white instead of red/green/yellow.

 

There are six cells in each row and I need them to read their corresponding revised date cell without having to format each cell independently.

 

I then want the same thing to happen with the revised and the actual boxes but will format that row separately.

 

MRobinson0297_1-1705078219232.png

 

 

best response confirmed by MRobinson0297 (Copper Contributor)
Solution

@MRobinson0297 

So let's say the Target dates are in F55:K55, the Revised dates in F56:K56 and the Actual dates in F57:K57.

Select F55:K56 (2 rows by 6 columns). F55 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=F56<>""

Click Format...
Activate the Fill tab.
Select white as highlight color.
Click OK, then click OK again.

 

P.S. If you only want to color a cell white if it contains a date and the cell below it also contains a date, use the following formula instead:

 

=AND(F55:F56<>"")

1 best response

Accepted Solutions
best response confirmed by MRobinson0297 (Copper Contributor)
Solution

@MRobinson0297 

So let's say the Target dates are in F55:K55, the Revised dates in F56:K56 and the Actual dates in F57:K57.

Select F55:K56 (2 rows by 6 columns). F55 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=F56<>""

Click Format...
Activate the Fill tab.
Select white as highlight color.
Click OK, then click OK again.

 

P.S. If you only want to color a cell white if it contains a date and the cell below it also contains a date, use the following formula instead:

 

=AND(F55:F56<>"")

View solution in original post