conditional formating based on other cell value

**tinkerbelle** · 05-06-2009, 05:31 AM

hI, i have a spreadsheet with the following:

Cell C9 contains a dropdown validation box and could contain:
Volume
Rate A
Rate B
Ratio A

Dependent on the option chosen, range D9:AM9 changes to show the correponding values based on a vlookup.

However the format can sometimes be wrong, for example:

Volume is an integer with no decimal place,
Rate A is a percentage with 1 decimal place
Rate B is also percentage with 1 decimal place
and Ratio A is an integer with 2 decimal places.

How can i get the format of A9:AM9 to alter depending on the value in
cell C9?

All help gratefully received.
Thanks

**mubashir aziz** · 05-06-2009, 05:36 AM

it will be easy for us if you attach a sample file with exactly what you want written in desired cells ...........

**JBeaucaire** · 05-06-2009, 10:55 AM

I hate to suggest simple IF/IF/IF stuff, but I can't think of anything else that isn't a VB solution.

The sample file shows this formula in action:

=IF($C9="Volume",INT(D6),IF(OR($C9="Rate A",$C9="Rate B"),TEXT(D6/100,"0.0%"),IF($C9="Ratio A",TEXT(D6,"0.00"))))

Obviously, you need to insert your VLOOKUP formula into the spots where I simply referred to another cell value (D6).

Anyway, this gives you an idea, you can adapt from here.

**tinkerbelle** · 05-06-2009, 01:34 PM

thanks for that, the excel function looks like it could work.
I will give it a go, thanks again.

**JBeaucaire** · 05-06-2009, 01:41 PM

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED].

(Also, use the "scales" icon across from the post dates to leave Feedback, it is appreciated)