Formatting one cell based on the contents of another

[agrandstaff]

Hello. I am trying to format one cell based on the contents of
another. Specifically, I have a drop-down menu in D11, there are about
10 different text lines you can choose from. Five of them are phrases
relating to percentages and the other five are phrases relating to
regular numbers. What I need to happen (preferably with a formula, not
a macro) is . . when you choose a phrase in D11 that deals with a
percetnage, I need D12 to be formatted as %, whereas if you choose a
phrase that deals with numbers it formats D12 as NUMBER. What I have
done so far is to use an OR statement in Conditional Formatting that
says: =OR(D11="Percentage Phrase 1",D11=Percentage Phrase 2", etc) and
then have it format the cell as a percentage (This is in Excel 2007 by
the way). If I leave cell D12 formatted as a number, you'd think it
would change it to a percentage only if one of the 'percentage
phrases' are chosen (and that part does work). The problem is, once
you've chosen a percentage phrase and it formats D12 as a percentage,
if you then choose a 'Number Phrase" it does not change the formatting
back to 'Number'. I even tried putting a 2nd conditional formatting
rule in, much like the first one but this one as =OR(D11="Number
Phrase 1", D11="Number Phrase 2", etc), but becasue the rules run in a
certain order once you've made one of each choice it stays with the
formatting of the 2nd choice and will not change back to the correct
one based on the drop-down choice. Any ideas?

 

[BoniM]

Remove any current conditional formats.
Format D12 as percentage, set conditional formatting as follows:
Conditional formatting, New rule, Use a formula to determine which cells to
format
Enter formula:
=left(d11,6)="Number"
format as your desired number format - Number with 2 decimals, etc.
If you select anything that begins with number in d11, should change to
numeric format, if you choose anything that starts with percent (actually,
anything except number) should return to original setting in percentage
format.
Good luck - have fun!

 

[BoniM]

Remove any current conditional formats.
Format D12 as percentage, set conditional formatting as follows:
Conditional formatting, New rule, Use a formula to determine which cells to
format
Enter formula:
=left(d11,6)="Number"
format as your desired number format - Number with 2 decimals, etc.
If you select anything that begins with number in d11, should change to
numeric format, if you choose anything that starts with percent (actually,
anything except number) should return to original setting in percentage
format.
Good luck - have fun!

 

[agrandstaff]

Remove any current conditional formats.
Format D12 as percentage, set conditional formatting as follows:
Conditional formatting, New rule, Use a formula to determine which cells to
format
Enter formula:
=left(d11,6)="Number"
format as your desired number format - Number with 2 decimals, etc.
If you select anything that begins with number in d11, should change to
numeric format, if you choose anything that starts with percent (actually,
anything except number) should return to original setting in percentage
format.
Good luck - have fun!





- Show quoted text -

Thanks for the help but this did not do it at all. I'm not sure if my
first message was clear. Every choice in the drop-down menu is a text
phrase. When certain of them are chosen I need the cell to be
formatted as a percentage. When the other certain ones are chosen I
need the cell to be formatted as a number.

 

[agrandstaff]

Thanks for the help but this did not do it at all. I'm not sure if my
first message was clear. Every choice in the drop-down menu is a text
phrase. When certain of them are chosen I need the cell to be
formatted as a percentage. When the other certain ones are chosen I
need the cell to be formatted as a number.- Hide quoted text -

- Show quoted text -

Anyone else on this one?