conditional format error Excel 2007

[Dr. Phil M]

I am trying to use conditional formatting to display a different number of
decimal places in a result based on the value of another variable in the same
worksheet. The data to calculate the result is entered in a separate
worksheet. The idea is that if the variable is less than a certain value
then one less digit is diplayed. This is triggered by the conditional format.
Otherise the default number of digits (ie one more) is displayed.
This seems to work fine where the data is input in the same worksheet but
where the input is in another worksheet as in my case then if the conditional
formatting is invoked the format of the cell does not change (revert to
default) if the variable rises above the target value.
F9 has no effect but if you delete a blank cell in the worksheet this
triggers the change. Recalculation is set on automatic.
Any ideas?

 

[Ron Rosenfeld]

On Wed, 19 Nov 2008 18:48:02 -0800, Dr. Phil M <Dr. Phil

I am trying to use conditional formatting to display a different number of
decimal places in a result based on the value of another variable in the same
worksheet. The data to calculate the result is entered in a separate
worksheet. The idea is that if the variable is less than a certain value
then one less digit is diplayed. This is triggered by the conditional format.
Otherise the default number of digits (ie one more) is displayed.
This seems to work fine where the data is input in the same worksheet but
where the input is in another worksheet as in my case then if the conditional
formatting is invoked the format of the cell does not change (revert to
default) if the variable rises above the target value.
F9 has no effect but if you delete a blank cell in the worksheet this
triggers the change. Recalculation is set on automatic.
Any ideas?

Please post your CF formula or rule, and also the version of Excel you are
using.

I had no problem in Excel 2007

Sheet1!A1: =Sheet2!A1*Sheet2!B1

I entered data in Sheet2!A1:B1 and Sheet1!A1 responded appropriately.

--ron

 

[Dr. Phil M]

Ron thanks! I'm using Ecel 2007.
The SS is a little complex but I made up a simple one which shows the error.
As below
Sheet 1 data entry in A1 and B1
Sheet 2
A1 =Sheet1!A1
A2=Sheet1!A2
A3=SUM(A1:A2)
Format sheet 2 A3 as a number with 1decimal place
Conditional format sheet 2 A3 with say 6 decimal places with condition
formula =$A$1=0
So if you enter 0 in sheet 1 A1 then see 6 decimal places in sheet 2 A3.
if you then enter 1 in sheet 1 A1, Excel does not reset sheet 2 A3 to 1
decimal place until you delete a blank cell on sheet 2.
Wierd?
Thanks
Phil

 

[Ron Rosenfeld]

Ron thanks! I'm using Ecel 2007.
The SS is a little complex but I made up a simple one which shows the error.
As below
Sheet 1 data entry in A1 and B1
Sheet 2
A1 =Sheet1!A1
A2=Sheet1!A2
A3=SUM(A1:A2)
Format sheet 2 A3 as a number with 1decimal place
Conditional format sheet 2 A3 with say 6 decimal places with condition
formula =$A$1=0
So if you enter 0 in sheet 1 A1 then see 6 decimal places in sheet 2 A3.
if you then enter 1 in sheet 1 A1, Excel does not reset sheet 2 A3 to 1
decimal place until you delete a blank cell on sheet 2.
Wierd?
Thanks
Phil

I can reproduce it now, and yes, it is extremely weird.

With regard to the conditional formatting, it seems to only affect the number
format. If, in addition to formatting the 6-decimal places in the conditional
format dialog box, you also format a fill color (for the same condition), the
fill color changes appropriately when the number format behaves as you
describe.

As you wrote, it is only an issue if the precedent cell refers to another
worksheet.

To me, all this implies that the Conditional Formatting is being triggered, but
there is some issue with the number formatting "going along".

I believe the ability to include number formatting in with the conditional
formatting was new in Excel 2007.

Seems to me to be a real bug.

Hopefully, one of the MVP's who has a contact with Microsoft for reporting bugs
will see this thread. If not, we should repost it.
--ron

 

[Bernard Liengme]

I can also reproduce this and I will report it to Microsoft
best wishes

 

[Ron Rosenfeld]

I can also reproduce this and I will report it to Microsoft
best wishes
--
Bernard Liengme
Microsoft Excel MVP
people.stfx.ca/bliengme
email address: remove uppercase characters

Great! Thank you, Bernard.
--ron

 

[Dr. Phil M]

Thanks Bernie,
I will have to stick with a workaround I used in previous versions of Excel.
Regards
Phil

 

[Dr. Phil M]

Thanks Ron for your help
Phil

Thanks Bernie,
I will have to stick with a workaround I used in previous versions of Excel.
Regards
Phil