Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Conditional Formatting - Excel

Status
Not open for further replies.

BC000

Technical User
May 16, 2006
31
AU
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?
 
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
 
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]
 
oooops - too early in the morning here - missed that [blush]

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
 
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]
 
Thanks macropod, your method worked.

Have a nice day.
 
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?
 
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]
 
Thanks macropod,

You've done it again.

Thanks again.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top