Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 7th 10, 12:04 AM posted to microsoft.public.excel.misc
smw smw is offline
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8
Default #N/A Conditional Formatting affects all cells?

I'm applying the conditional format =ISERROR($A$1) to change the text of
cells displaying #N/A to white, as recommended by every conditional
formatting guide on the Internet. However, when applied to a range of cells,
they all turn white!!

Cell A1 has a formula that provides a #N/A error, in the same way that the
desired cells produce an #N/A error:

The cells that need to be white are coded =VLOOKUP(B4,SHEET1,10) where B4 is
='SHEET2'!C4. C4 is blank, so B4 returns 0. The result is #N/A. (When C4 is
no longer blank, B4 displays data and needs to be visible.)

This is the only conditional format used in the spreadsheet. I've tried to
find out if anyone else has this problem and had no luck.

  #2   Report Post  
Old January 7th 10, 12:28 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 703
Default #N/A Conditional Formatting affects all cells?

Hi

Your problem is that you are using an absolute cell reference, change
the formula to =ISERROR(A1)

Notice the $ signs has been removed to indicate that it is a relative
reference.

Regards,
Per


On 7 Jan., 01:04, SMW wrote:
I'm applying the conditional format =ISERROR($A$1) to change the text of
cells displaying #N/A to white, as recommended by every conditional
formatting guide on the Internet. *However, when applied to a range of cells,
they all turn white!!

Cell A1 has a formula that provides a #N/A error, in the same way that the
desired cells produce an #N/A error:

The cells that need to be white are coded =VLOOKUP(B4,SHEET1,10) where B4 is
='SHEET2'!C4. C4 is blank, so B4 returns 0. *The result is #N/A. *(When C4 is
no longer blank, B4 displays data and needs to be visible.)

This is the only conditional format used in the spreadsheet. *I've tried to
find out if anyone else has this problem and had no luck. *


  #3   Report Post  
Old January 7th 10, 12:38 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default #N/A Conditional Formatting affects all cells?

I would not use ISERROR which masks all errors but then I'm not an Internet
guide.

I would use ISNA which is specific to your needs.

Your formula has absolute reference to $A$1

Each cell in the range looks only at $A$1 for the condtion.

Remove the $ signs.

=ISNA(A1)


Gord Dibben MS Excel MVP

On Wed, 6 Jan 2010 16:04:01 -0800, SMW
wrote:

I'm applying the conditional format =ISERROR($A$1) to change the text of
cells displaying #N/A to white, as recommended by every conditional
formatting guide on the Internet. However, when applied to a range of cells,
they all turn white!!

Cell A1 has a formula that provides a #N/A error, in the same way that the
desired cells produce an #N/A error:

The cells that need to be white are coded =VLOOKUP(B4,SHEET1,10) where B4 is
='SHEET2'!C4. C4 is blank, so B4 returns 0. The result is #N/A. (When C4 is
no longer blank, B4 displays data and needs to be visible.)

This is the only conditional format used in the spreadsheet. I've tried to
find out if anyone else has this problem and had no luck.


  #4   Report Post  
Old January 7th 10, 01:16 AM posted to microsoft.public.excel.misc
smw smw is offline
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8
Default #N/A Conditional Formatting affects all cells?

I could have sworn I tried that! It works now, thanks so much!!

"Per Jessen" wrote:

Hi

Your problem is that you are using an absolute cell reference, change
the formula to =ISERROR(A1)

Notice the $ signs has been removed to indicate that it is a relative
reference.

Regards,
Per


On 7 Jan., 01:04, SMW wrote:
I'm applying the conditional format =ISERROR($A$1) to change the text of
cells displaying #N/A to white, as recommended by every conditional
formatting guide on the Internet. However, when applied to a range of cells,
they all turn white!!

Cell A1 has a formula that provides a #N/A error, in the same way that the
desired cells produce an #N/A error:

The cells that need to be white are coded =VLOOKUP(B4,SHEET1,10) where B4 is
='SHEET2'!C4. C4 is blank, so B4 returns 0. The result is #N/A. (When C4 is
no longer blank, B4 displays data and needs to be visible.)

This is the only conditional format used in the spreadsheet. I've tried to
find out if anyone else has this problem and had no luck.


.

  #5   Report Post  
Old January 7th 10, 01:41 AM posted to microsoft.public.excel.misc
smw smw is offline
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8
Default #N/A Conditional Formatting affects all cells?

I spoke too soon! When the value of C4 changes to something besides #N/A,
the text does not change back from white. So changing the cells to a
relative reference does not make conditional formatting work...

"Per Jessen" wrote:

Hi

Your problem is that you are using an absolute cell reference, change
the formula to =ISERROR(A1)

Notice the $ signs has been removed to indicate that it is a relative
reference.

Regards,
Per


On 7 Jan., 01:04, SMW wrote:
I'm applying the conditional format =ISERROR($A$1) to change the text of
cells displaying #N/A to white, as recommended by every conditional
formatting guide on the Internet. However, when applied to a range of cells,
they all turn white!!

Cell A1 has a formula that provides a #N/A error, in the same way that the
desired cells produce an #N/A error:

The cells that need to be white are coded =VLOOKUP(B4,SHEET1,10) where B4 is
='SHEET2'!C4. C4 is blank, so B4 returns 0. The result is #N/A. (When C4 is
no longer blank, B4 displays data and needs to be visible.)

This is the only conditional format used in the spreadsheet. I've tried to
find out if anyone else has this problem and had no luck.


.



  #6   Report Post  
Old January 7th 10, 01:50 AM posted to microsoft.public.excel.misc
smw smw is offline
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8
Default #N/A Conditional Formatting affects all cells?

I tried ISNA(A1) - both by selecting all cells and applying the conditional
format, and then by selecting individual cells and giving each one the same
formula under conditional formatting. The formatting did not change back
from white when #N/A changed to a valid data value.

(I'm using "Formula is" rather than "Cell Value is.")

"Gord Dibben" wrote:

I would not use ISERROR which masks all errors but then I'm not an Internet
guide.

I would use ISNA which is specific to your needs.

Your formula has absolute reference to $A$1

Each cell in the range looks only at $A$1 for the condtion.

Remove the $ signs.

=ISNA(A1)


Gord Dibben MS Excel MVP

On Wed, 6 Jan 2010 16:04:01 -0800, SMW
wrote:

I'm applying the conditional format =ISERROR($A$1) to change the text of
cells displaying #N/A to white, as recommended by every conditional
formatting guide on the Internet. However, when applied to a range of cells,
they all turn white!!

Cell A1 has a formula that provides a #N/A error, in the same way that the
desired cells produce an #N/A error:

The cells that need to be white are coded =VLOOKUP(B4,SHEET1,10) where B4 is
='SHEET2'!C4. C4 is blank, so B4 returns 0. The result is #N/A. (When C4 is
no longer blank, B4 displays data and needs to be visible.)

This is the only conditional format used in the spreadsheet. I've tried to
find out if anyone else has this problem and had no luck.


.

  #7   Report Post  
Old January 7th 10, 05:27 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 2,389
Default #N/A Conditional Formatting affects all cells?

What did you change it to? Iserror traps any error condition including #N/A.
If you entered a value, like 3, and still had white text, it means you
didn't enter the conditional format correctly, as the recommended formula is
correct.

By the way, I presume that changing the text to white makes it disappear. If
that's what you want, you're better off using formulas like:
=if(isna(yourformula),"",yourformula)

Regards
Fred

"SMW" wrote in message
...
I spoke too soon! When the value of C4 changes to something besides #N/A,
the text does not change back from white. So changing the cells to a
relative reference does not make conditional formatting work...

"Per Jessen" wrote:

Hi

Your problem is that you are using an absolute cell reference, change
the formula to =ISERROR(A1)

Notice the $ signs has been removed to indicate that it is a relative
reference.

Regards,
Per


On 7 Jan., 01:04, SMW wrote:
I'm applying the conditional format =ISERROR($A$1) to change the text
of
cells displaying #N/A to white, as recommended by every conditional
formatting guide on the Internet. However, when applied to a range of
cells,
they all turn white!!

Cell A1 has a formula that provides a #N/A error, in the same way that
the
desired cells produce an #N/A error:

The cells that need to be white are coded =VLOOKUP(B4,SHEET1,10) where
B4 is
='SHEET2'!C4. C4 is blank, so B4 returns 0. The result is #N/A. (When
C4 is
no longer blank, B4 displays data and needs to be visible.)

This is the only conditional format used in the spreadsheet. I've
tried to
find out if anyone else has this problem and had no luck.


.


  #8   Report Post  
Old January 7th 10, 02:23 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 2,389
Default #N/A Conditional Formatting affects all cells?

Do you need to recalculate? Is recalculation set to automatic?
In 2007, check for:
Office buttonExcel OptionsFormulasWorkbook CalculationAutomatic

Regards,
Fred

"SMW" wrote in message
...
I tried ISNA(A1) - both by selecting all cells and applying the conditional
format, and then by selecting individual cells and giving each one the
same
formula under conditional formatting. The formatting did not change back
from white when #N/A changed to a valid data value.

(I'm using "Formula is" rather than "Cell Value is.")

"Gord Dibben" wrote:

I would not use ISERROR which masks all errors but then I'm not an
Internet
guide.

I would use ISNA which is specific to your needs.

Your formula has absolute reference to $A$1

Each cell in the range looks only at $A$1 for the condtion.

Remove the $ signs.

=ISNA(A1)


Gord Dibben MS Excel MVP

On Wed, 6 Jan 2010 16:04:01 -0800, SMW
wrote:

I'm applying the conditional format =ISERROR($A$1) to change the text of
cells displaying #N/A to white, as recommended by every conditional
formatting guide on the Internet. However, when applied to a range of
cells,
they all turn white!!

Cell A1 has a formula that provides a #N/A error, in the same way that
the
desired cells produce an #N/A error:

The cells that need to be white are coded =VLOOKUP(B4,SHEET1,10) where
B4 is
='SHEET2'!C4. C4 is blank, so B4 returns 0. The result is #N/A. (When
C4 is
no longer blank, B4 displays data and needs to be visible.)

This is the only conditional format used in the spreadsheet. I've tried
to
find out if anyone else has this problem and had no luck.


.


  #9   Report Post  
Old January 8th 10, 12:51 AM posted to microsoft.public.excel.misc
smw smw is offline
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8
Default #N/A Conditional Formatting affects all cells?

Yup, it's on auto!!

"Fred Smith" wrote:

Do you need to recalculate? Is recalculation set to automatic?
In 2007, check for:
Office buttonExcel OptionsFormulasWorkbook CalculationAutomatic

Regards,
Fred

"SMW" wrote in message
...
I tried ISNA(A1) - both by selecting all cells and applying the conditional
format, and then by selecting individual cells and giving each one the
same
formula under conditional formatting. The formatting did not change back
from white when #N/A changed to a valid data value.

(I'm using "Formula is" rather than "Cell Value is.")

"Gord Dibben" wrote:

I would not use ISERROR which masks all errors but then I'm not an
Internet
guide.

I would use ISNA which is specific to your needs.

Your formula has absolute reference to $A$1

Each cell in the range looks only at $A$1 for the condtion.

Remove the $ signs.

=ISNA(A1)


Gord Dibben MS Excel MVP

On Wed, 6 Jan 2010 16:04:01 -0800, SMW
wrote:

I'm applying the conditional format =ISERROR($A$1) to change the text of
cells displaying #N/A to white, as recommended by every conditional
formatting guide on the Internet. However, when applied to a range of
cells,
they all turn white!!

Cell A1 has a formula that provides a #N/A error, in the same way that
the
desired cells produce an #N/A error:

The cells that need to be white are coded =VLOOKUP(B4,SHEET1,10) where
B4 is
='SHEET2'!C4. C4 is blank, so B4 returns 0. The result is #N/A. (When
C4 is
no longer blank, B4 displays data and needs to be visible.)

This is the only conditional format used in the spreadsheet. I've tried
to
find out if anyone else has this problem and had no luck.

.


.

  #10   Report Post  
Old January 8th 10, 01:12 AM posted to microsoft.public.excel.misc
smw smw is offline
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8
Default #N/A Conditional Formatting affects all cells?

I entered a value that is within the VLOOKUP range (293, which should produce
"John Doe"). When I select the range of cells, I see it has a non-error
value, but the text is still white.

HOWEVER I tried the IF(ISNA) formula and it holds up under testing, making
the text disappear when the formula produces an error!! Thanks a million

"Fred Smith" wrote:

What did you change it to? Iserror traps any error condition including #N/A.
If you entered a value, like 3, and still had white text, it means you
didn't enter the conditional format correctly, as the recommended formula is
correct.

By the way, I presume that changing the text to white makes it disappear. If
that's what you want, you're better off using formulas like:
=if(isna(yourformula),"",yourformula)

Regards
Fred

"SMW" wrote in message
...
I spoke too soon! When the value of C4 changes to something besides #N/A,
the text does not change back from white. So changing the cells to a
relative reference does not make conditional formatting work...

"Per Jessen" wrote:

Hi

Your problem is that you are using an absolute cell reference, change
the formula to =ISERROR(A1)

Notice the $ signs has been removed to indicate that it is a relative
reference.

Regards,
Per


On 7 Jan., 01:04, SMW wrote:
I'm applying the conditional format =ISERROR($A$1) to change the text
of
cells displaying #N/A to white, as recommended by every conditional
formatting guide on the Internet. However, when applied to a range of
cells,
they all turn white!!

Cell A1 has a formula that provides a #N/A error, in the same way that
the
desired cells produce an #N/A error:

The cells that need to be white are coded =VLOOKUP(B4,SHEET1,10) where
B4 is
='SHEET2'!C4. C4 is blank, so B4 returns 0. The result is #N/A. (When
C4 is
no longer blank, B4 displays data and needs to be visible.)

This is the only conditional format used in the spreadsheet. I've
tried to
find out if anyone else has this problem and had no luck.

.


.



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
Inserting a new column affects conditional formatting tino2009 New Users to Excel 9 June 29th 09 10:18 AM
Conditional formatting affects text as well as values SueG Excel Discussion (Misc queries) 7 March 2nd 09 02:12 PM
Formatting cells in a column with conditional formatting? shamor Excel Discussion (Misc queries) 8 May 19th 08 10:11 PM
Drop-Down List Choice Affects Multiple Cells? Patrick R Excel Worksheet Functions 5 November 24th 06 12:33 AM
The cursor affects all cells it touches-how to correct? Art Bair New Users to Excel 1 January 5th 06 07:31 PM


All times are GMT +1. The time now is 11:51 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017