Solved

# Conditional Formatting

Posted on 2014-10-28
310 Views
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
Question by:zerog
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 6

Expert Comment

ID: 40409046
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" ) ) ))
``````
0

LVL 6

Accepted Solution

Brian Matis earned 500 total points
ID: 40409050
Here's a revised version of the spreadsheet with the updates. Is this what you meant?
Scoresheet-2.xlsx
0

LVL 19

Expert Comment

ID: 40409078
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.