Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting one cell based on the contents of another
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting one cell based on the contents of another
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! " wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting one cell based on the contents of another
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! " wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting one cell based on the contents of another
On Apr 12, 2:10 am, BoniM wrote:
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! " wrote: 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?- Hide quoted text - - 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting one cell based on the contents of another
On Apr 12, 7:18 pm, wrote:
On Apr 12, 2:10 am, BoniM wrote: 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! " wrote: 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?- Hide quoted text - - 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.- Hide quoted text - - Show quoted text - Anyone else on this one? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I add a cell based on another cells contents? | Excel Discussion (Misc queries) | |||
Can I format a row based on the contents of one cell? | Excel Discussion (Misc queries) | |||
Delete row based on contents of cell | Excel Discussion (Misc queries) | |||
Add Rows based on Cell contents | Excel Discussion (Misc queries) | |||
Sum numbers based on the contents of another cell | Excel Worksheet Functions |