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

[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

 

[Niek Otten]

Use a formula like =ISNA(A1) and set the font color to white in conditional
formatting

 

[Car Builder]

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

 

[Ron Rosenfeld]

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.

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

--ron

 

[Mike]

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

 

[T. Valko]

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.

 

[Mike]

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

 

[Ron Rosenfeld]

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

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

 

[Niek Otten]

Thanks for pointing that out, Ron. I realise now my recommendation was not
too clear.

 

[Mike]

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

 

[Ron Rosenfeld]

Cond Formatting always puts quotes around my
formulas and also $ signs to make the formula focus on one cell only.

I don't know why it does that. But I just manually remove the quotes and the
$'s.
--ron

 

[Mike]

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

 

[Bernie Deitrick]

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

 

[Niek Otten]

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

 

[Ron Rosenfeld]

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.

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"

2. Choose: Use a formula to determine which cells to format.

3. Choose: Cell Value "equal to" and then enter =ISNA(B4)

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)

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

Rule Format Applies to
Formula: =ISNA(B2) =$B$2:$B$6
--ron

 

[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

 

[Ron Rosenfeld]

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

Glad to help.

Thanks for the feedback.
--ron

 

[herochenna]

Visit www.ExcelGoodies.Com

 

[herochenna]

Visit www.ExcelGoodies.Com