N/A - conditional format so as not to appear in screen

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have a chart to show values for the current month.

In order to show nothing in the chart for the rest of the month, the formula
shows "n/a". This makes the lines within the chart run up to the current day,
but the graph to show blank for the rest of the month. This is our intent.

However, I want the "n/a" in the standard worksheet to be invisible to the
user through formatting. However, I cannot get conditional formatting to
work. Can anyone help?

Thanks!
Mike
 
Use a formula like =ISNA(A1) and set the font color to white in conditional
formatting
 
It depends which version of Excel you are using, but in general terms make it an "if" statement and then in the non matching result put "". This will cause the formulae to enter a blank value.

There are more complex uses of functions to get rid of the N/A, but this is the simple one and usually does the trick.



Mike wrote:

N/A - conditional format so as not to appear in screen
14-Nov-09

I have a chart to show values for the current month

In order to show nothing in the chart for the rest of the month, the formul
shows "n/a". This makes the lines within the chart run up to the current day
but the graph to show blank for the rest of the month. This is our intent

However, I want the "n/a" in the standard worksheet to be invisible to th
user through formatting. However, I cannot get conditional formatting t
work. Can anyone help

Thanks
Mike

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Add Windows GridView control in WPF dynamically in C#.
http://www.eggheadcafe.com/tutorial...1-bf33de76bd69/add-windows-gridview-cont.aspx
 
It depends which version of Excel you are using, but in general terms make it an "if" statement and then in the non matching result put "". This will cause the formulae to enter a blank value.

There are more complex uses of functions to get rid of the N/A, but this is the simple one and usually does the trick.
Click to expand...

Except that if he does this, the graph won't appear as desired.

--ron
 
That's right! I need to keep the N/A so that the graph is right. The need is
to have the N/A "invisible" (ie. white) so that the user does not see it.

Any ideas?

Mike
 
Did you see Niek's reply?

Use conditional formatting. Use the Formula Is option and use a formula
like: =ISNA(A1). Set the cells font color to be the same as the background
color.
 
Unfortunately, ISNA(A1) does not work. This makes the answer FALSE, which
shows up as 0 on the graph.

Essentially, N/A must stay so that there is no data plot on the graph.
The challenge: The cell must show up as invisible in the cell.

Thanks,
Mike
 
Unfortunately, ISNA(A1) does not work. This makes the answer FALSE, which
shows up as 0 on the graph.
Click to expand...

You misunderstood Niek's recommendation.

The ISNA(A1) formula goes into the conditional formatting dialog, NOT into the
cell.

Depending on your Excel version, you first select "Conditional Formatting".
After you do this, you will find a dialog box where you can enter that formula,
and then also set the formatting so that the font color is the same a the
background.

If you can't find this, post your version of Excel and someone will give you
specifics.
--ron
 
Thanks for pointing that out, Ron. I realise now my recommendation was not
too clear.
 
Ron, Niek, Biff & Group,

Great! It looks like I am ver close, but I am having trouble with Cond
Formatting. I am using Office 2007, but this file I am creating will also be
used in Office 2003.

In 2007, how do I set this up? Cond Formatting always puts quotes around my
formulas and also $ signs to make the formula focus on one cell only.

Thanks so much!
Mike
 
Cond Formatting always puts quotes around my
formulas and also $ signs to make the formula focus on one cell only.
Click to expand...

I don't know why it does that. But I just manually remove the quotes and the
$'s.
--ron
 
Unfortunately, I cannot get that to work. :-(

Here are the exact steps I am doing in Office 2007. I am even trying to do
it for just one cell, and can't get it to work.

1. Go to Conditional Formatting (New or Manage Rules & Add)
2. Choose: "Format only cells that contain"
3. Choose: Cell Value "equal to" and then enter =ISNA(B4)
4.Change formatting (I made background to be blue just to make it obvious)

When I try this, I get no results.

Here is my data....
Date Value
10/16/09 1
10/17/09 2
10/18/09 #N/A
10/19/09 4
10/20/09 5

This is tougher than I thought!

Mike
 
Mike,

Don't use the Cell Value option - use the "Formula is" option: I'm not sure
where in XL2007 it is, but it is one of the last (if not THE last) option in
the CF dialog.

Bernie
 
Assuming that it is all about cell A1:
Home>Conditional Formatting>Clear Rules>Clear rules from selected cells
Conditional Formatting>New Rule>Use a formula to determine which cells to
format>In the formula box, enter: =ISNA(A1)>Click Format> on the Font tab,
click Color white

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
 
Unfortunately, I cannot get that to work. :-(

Here are the exact steps I am doing in Office 2007. I am even trying to do
it for just one cell, and can't get it to work.
Click to expand...

0. Select the range to be formatted. In this example B2:B6
1. Go to Conditional Formatting (New or Manage Rules & Add)

2. Choose: "Format only cells that contain"
Click to expand...

2. Choose: Use a formula to determine which cells to format.
3. Choose: Cell Value "equal to" and then enter =ISNA(B4)
Click to expand...

3. Enter: =ISNA(B2) (where B2 is the first cell in the range you selected in
Step 0
4.Change formatting (I made background to be blue just to make it obvious)
Click to expand...

When you look at the Conditional Formatting Rules Manager, it should show

Rule Format Applies to
Formula: =ISNA(B2) =$B$2:$B$6
--ron
 
Thank you team! You have truly delivered tha answer, once again! And have
been a great help. It also transferred from Office 2007 to Office 2003
computers without issue!

I can't believe I did not see the "Formula" section in Conditional
Formatting in Office 2007.

Best wishes!
Mike
 
Thank you team! You have truly delivered tha answer, once again! And have
been a great help. It also transferred from Office 2007 to Office 2003
computers without issue!

I can't believe I did not see the "Formula" section in Conditional
Formatting in Office 2007.

Best wishes!
Mike
Click to expand...


Glad to help.

Thanks for the feedback.
--ron
 
Back
Top