• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Conditional Formatting

I have an Excel workbook with two sheets. The “Mark” sheet allows you to capture the names and marks. The “Level” sheet displays the level for the entered mark in the first sheet. The problem I have is that for the rows that have no names the cell is still formatted. It should only do this for rows that have names. See attached file.
Scoresheet.xlsx
0
zerog
Asked:
zerog
  • 2
1 Solution
 
Brian MatisProduct ManagerCommented:
The problem is with the IF statements in column B on the Level tab. You need to define what should happen when the cells on the Mark tab are blank. Here's an IF statement you can use that'll set blank values on the Mark tab to being blank values on the level tab.

=IF(Mark!B4="", "", IF( Mark!B4<=39, "1", IF( Mark!B4<=69, "2", IF( Mark!B4<=100, "3" ) ) ))

Open in new window

0
 
Brian MatisProduct ManagerCommented:
Here's a revised version of the spreadsheet with the updates. Is this what you meant?
Scoresheet-2.xlsx
0
 
MontoyaProcess Improvement MgrCommented:
you can change your formula to: =IF(ISBLANK(Mark!A4)=FALSE,(IF( Mark!B4<=39, "1", IF( Mark!B4<=69, "2", IF( Mark!B4<=100, "3" ) ) )),0)
this basically checks whether or not the name exists, before performing your conditional formatting.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now