Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 29th 17, 06:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2017
Posts: 1
Default Conditional formatting and If statements

So what I originally had was some conditional formatting for tracking progress. An X made the cell turn green, an O made the cell turn blue.

The issues was the formula I used to populate those cells (vlookup) causes many #N/A cells. I wanted to hide these because they clutter up the space.

I found if you use an IF statement you can hide contents of the cell
=IF(ISNA(formula)),"",formula)

Now the issue is my conditional formatting won't work. The cells that should have Xs and Os do but they aren't changing color.

Anyone with some sage advice for me?
An alternate way to hide NA cells or to get formatting to work again?

My full formula is this
=IF(ISNA(vlookup($B4,'Job Database'!$A:$F,4,false)),"",vlookup($B4,'Job Database'!$A:$F,4,false))

  #2   Report Post  
Old August 29th 17, 08:17 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,058
Default Conditional formatting and If statements

So what I originally had was some conditional formatting for tracking
progress. An X made the cell turn green, an O made the cell turn blue.

The issues was the formula I used to populate those cells (vlookup) causes
many #N/A cells. I wanted to hide these because they clutter up the space.

I found if you use an IF statement you can hide contents of the cell
=IF(ISNA(formula)),"",formula)

Now the issue is my conditional formatting won't work. The cells that should
have Xs and Os do but they aren't changing color.

Anyone with some sage advice for me?
An alternate way to hide NA cells or to get formatting to work again?

My full formula is this
=IF(ISNA(vlookup($B4,'Job Database'!$A:$F,4,false)),"",vlookup($B4,'Job
Database'!$A:$F,4,false))


Try a 3rd CF Formula: =ISERROR(A1) or whatever the cell address is, then
format the font color to match the cell fill color!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatting with IF statements Eric D Excel Discussion (Misc queries) 9 March 19th 12 07:29 PM
If Statements??/Conditional formatting from drop-down multiple she sarah Excel Worksheet Functions 1 June 25th 09 05:51 AM
Conditional statements james Excel Worksheet Functions 9 January 7th 08 03:07 PM
Conditional formatting combined with multiple IF statements Hendrik Excel Worksheet Functions 7 March 22nd 07 06:03 PM
Nesting IF statements, Conditional Formatting Nick Danger Excel Discussion (Misc queries) 2 September 26th 05 10:31 PM


All times are GMT +1. The time now is 12:08 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017