Conditional Formatting - Excel

[BC000]

In a spreadsheet, if cell D5="Barrels", I would like cells A1 to A6 to be formatted to 1 decimal place, but if cell D5="Metric Tons", I would like cells A1 to A6 to be formatted to 3 decimal places.

Cells A1 to A6 have a formula in them and are not blank.

Any simple way of doing this?

 

[xlbo]

yup

Select A1:A6

Go Format>Conditional Formatting

Change "Value Is" to "Formula Is"

enter your IF formulae to test D5

et voila

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[macropod]

Hi BC000,

For the values in A1:A6 try something based on:
=VALUE(TEXT(A1,IF($D$5="Metric Tons","#,##0.000","#,##0.0")))
As coded, the formula looks at the contents of A1 & D5 (ie you'd be using it in, say, B1:B6). To get this to work with the relevant values instead (ie in A1:A6 directly), you'd have to replace the 'A1' reference with the relevant value.

Another way would be to use an event-driven macro. If you'd prefer that approach, post back.

xlbo: I don't see any numeric formatting options in Conditional Formatting (XL2000)

Cheers

[MS MVP - Word]

 

[xlbo]

oooops - too early in the morning here - missed that

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[macropod]

Yeah,

I have days like that too. Sometimes, a whole week's worth seem to gang up on me all at the same time ...

Cheers

[MS MVP - Word]

 

[BC000]

Thanks macropod, your method worked.

Have a nice day.

 

[BC000]

A slight problem macropod. Your method seems to exclude trailing zeroes. E.g. if the result of the formula in the subject cells is 167.400, the text value copied in cell A1 shows up as 167.4. The value under it might show all three decimals e.g. 3456.789 (because there are no trailing zeroes). This makes the column look disorderly, with some entries with two decimals showing, and some with three and some with no decimals showing (if all three decimals are zeroes).

Can you please suggest a solution to this dilema?

 

[macropod]

Hi BC000

If you remove the VALUE funtion, it'll display correctly:
=TEXT(A1,IF($D$5="Metric Tons","#,##0.000","#,##0.0"))
I was trying to be too clever, by keeping the displayed result a number. Now it'll be a text string.

Cheers

[MS MVP - Word]

 

[BC000]

Thanks macropod,

You've done it again.

Thanks again.