Conditional Format for Numbers

Is it possible in Excel to conditionally format a Number type in a cell
based on another cell?

Example

A1 is a dropdown with "Variance $" and "Variance %"

And in A2 I have an IF statement that that will give a result from two
other cells.  What I want A2 to show is either $#.## or ##% depending
on what I have in the drop down.

Any help would be great.

Thanks

Tyson

0
tmunee (20)
6/27/2005 7:36:59 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
567 Views

Similar Articles

[PageSpeed] 17

On 27 Jun 2005 12:36:59 -0700, "Tyson" <tmunee@hotmail.com> wrote:

>Is it possible in Excel to conditionally format a Number type in a cell
>based on another cell?
>
>Example
>
>A1 is a dropdown with "Variance $" and "Variance %"
>
>And in A2 I have an IF statement that that will give a result from two
>other cells.  What I want A2 to show is either $#.## or ##% depending
>on what I have in the drop down.
>
>Any help would be great.
>
>Thanks
>
>Tyson

In order to actually FORMAT the cell, you would have to either guarantee
non-overlapping values for $ vs %, which you could then test, or use a VBA
event-triggered macro.

To have it display the way you want, a simple formula can do that; but the
result is a text string and may not be useable by some functions, directly.

=IF(A1="Variance %", TEXT(your_formula,"0.00%"),TEXT(your_formula,"$0.00"))

If this is not satisfactory, post back and we'll derive a VBA solution.


--ron
0
ronrosenfeld (3122)
6/27/2005 7:51:57 PM

Ron Rosenfeld wrote:
> On 27 Jun 2005 12:36:59 -0700, "Tyson" <tmunee@hotmail.com> wrote:
>
> >Is it possible in Excel to conditionally format a Number type in a cell
> >based on another cell?
> >
> >Example
> >
> >A1 is a dropdown with "Variance $" and "Variance %"
> >
> >And in A2 I have an IF statement that that will give a result from two
> >other cells.  What I want A2 to show is either $#.## or ##% depending
> >on what I have in the drop down.
> >
> >Any help would be great.
> >
> >Thanks
> >
> >Tyson
>
> In order to actually FORMAT the cell, you would have to either guarantee
> non-overlapping values for $ vs %, which you could then test, or use a VBA
> event-triggered macro.
>
> To have it display the way you want, a simple formula can do that; but the
> result is a text string and may not be useable by some functions, directly.
>
> =IF(A1="Variance %", TEXT(your_formula,"0.00%"),TEXT(your_formula,"$0.00"))
>
> If this is not satisfactory, post back and we'll derive a VBA solution.
> 
> 
> --ron


Perfect!

Thanks Ron.

Tyson

0
tmunee (20)
6/27/2005 8:31:04 PM
On 27 Jun 2005 13:31:04 -0700, "Tyson" <tmunee@hotmail.com> wrote:

>
>

>>
>> =IF(A1="Variance %", TEXT(your_formula,"0.00%"),TEXT(your_formula,"$0.00"))
>>
>> If this is not satisfactory, post back and we'll derive a VBA solution.
>> 
>> 
>> --ron
>
>
>Perfect!
>
>Thanks Ron.
>
>Tyson

You're welcome.  Glad to help.  Thanks for the feedback.

--ron
0
ronrosenfeld (3122)
6/28/2005 1:08:36 AM
Reply:

Similar Artilces:

Cond formatting help.
Hi All How can I apply cond.formatting below ? If col A and col B empty no format If both col A and col B with data no format If only one of them is with data then formatted Thank you very much in advance. With A1:Bxxx selected and A1 the active cell Format|conditional formatting formula is: =counta($a1:$b1)=1 rumkus@hotmail.com wrote: > > Hi All > > How can I apply cond.formatting below ? > > If col A and col B empty no format > If both col A and col B with data no format > If only one of them is with data then formatted > > Thank you very much in ...

Problem entering numbers
Hello, I am having an awkward problem, on every sheet I use(Including new ones).. When I enter 100 it changes it to 1. When I enter 1000 it changes it to 10 When I enter 10000 it changes it to 100. I thought it was something to do with iteration in the tools>options menu but I have set both of them to 1 and they are unticked anyway and it shows the same problem. Can anyone help please?!?!? Thanks in advance John -- johncassell ------------------------------------------------------------------------ johncassell's Profile: http://www.excelforum.com/member.php?action=getinfo&u...

Upload customer info stored in CSV format directly to RMS
We are undertaking a drop ship program. We receive order notification through a program called VendorNet. We also submit tracking info and invoices through VendorNet. We want to automatically dump customer info from VendorNet to RMS. We need to be able to upload a .csv file into RMS, and provide a map of the layout of the order of the fields that the .csv file needs to have to successfully sync with RMS. Any suggestion or a lead to a third party program is very well appreciated. Krish Krish, If you have SQL Server, DTS packages are well suited for this type of thing. - Evan Culver...

text format
Hi All by a sudden all incomming mail with text format are attached as a text file. I don't remember me doing anything.. Other mail like html is not attached as file. Anyone seen the same ? Setup: Outlook 2000 (only popmail) on windows 2000 and McAfee Enterpise 7.0 Best Regards /martin Uppsala Sweden ...

Format pasting using keyboard shortcuts
Hi, I am working on a Spreadsheet project where I have to use the keyboard to Copy & Paste a Format from one cell to another. Does anyone know how this can be done. In Word to Copy & Paste a Format it is CTRL Shift C, CTRL Shift V which does not appear to be the case in Excel. Regards, Lynda Hi Griff not as easy as Word but Control & C to copy Alt & E then S then T and enter to paste format (personally, i would use the format painter icon on the standard toolbar) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway &quo...

Excel VBA
I have a drop-down list that populates from a range of cells. This rang of cells includes criteria for several regions: test1 test2 test3 test4 test5 test6 test7 test8 test9 What I need to do is if user selects Region 1, drop-down displays: test1 test2 test3 If user selects Region 2, drop-down displays: test4 test5 test6 If user selects Region 3, drop-down displays: test7 test8 test9 Should I break up the lists into separate lists and use an II statement? If so, can someone show me how I would implement this? Or is the best solution for this a macro or VBA module. I'm not sure ho to ap...

Comma format button decimals
I find myself annoyingly frequently clicking the comma style button and then the reduce decimals twice to get whole number with comma formatting. I'd like to either make a new button or get this one to can the decimals. ...

Negative numbers #4
I need to be able to import negative numbers, but when they have right hyphens, they are considered text not negative numbers. I've tried to change the "regional settings" for negative signs, but it's made no difference. What can I do? Thanks, Blake Blake During the import, on the last panel there is an "advanced" button. Click that and you'll see a check box for trailing minus signs Ar ----- Blake Witten wrote: ---- I need to be able to import negative numbers, but when they have right hyphens, they are consid...

Formatting Photos
how do you get the edges of photos to look faded or foggy? i have tried every thing. do i need to go to a different program or is there something i can use in publisher. Tracie wrote: > how do you get the edges of photos to look faded or > foggy? i have tried every thing. do i need to go to a > different program or is there something i can use in > publisher. ======================================= I'm thinking your best bet would be to use an image editing program to prepare your image. For example...in MSPicture It! you would simply go to...Soft Edges...and drag the sli...

Format Cells #11
In one particular worksheet If I right click on a cell and click on "Format Cells..." the format cell window does not show up. The worksheet is not protected and there is no conditional formatting. If I create a new worksheet, rt. click on a cell and select format cell.. the popup works fine. what is a-miss with this spread sheet? ...

conditional formatting limitation
Is there any way I can increase the limit of 3 when conditional formatting? I am building a schedule and I wish to colour code unique id's. The number of unique id's exceeds three. Thank-you Hi rekoop! Apart from conditional formats and the default format you do have ability to control font formats using the custom formats usually reserved for positive, negative and zero. See: http://www.mcgimpsey.com/excel/conditional6.html But in most cases where you need more than three formats for your conditions, you will need to resort to VBA. For this, see: Dave McRitchie: http://w...

new publisher format troubles
Hello All, I currently have our church bulletin in Publisher 2000. It is run on 8.5 x 11 with 4 pages and book fold, so there are two pages on each side of the paper (using duplex printing with lazer printer). I want to keep this exact format, but I want to switch to 8.5 x 14, and use the extra three inches for 2 separate pages (one front and one back). So, in essence, everything would stay the same except for the new few inches that are gained. The document is setup to print in landscape form with book fold. The pages are obviously 8.5 inches tall, and 5.5 inches wide. I can change ...

conditional colour formating of adjacent cells
I am trying to format a cell if adjacent cells satisfy certain criteria For example if cell a1="blue" and b1>1000 and c1="horses" then colou d1 orange, or some other constant predetermined colour withou affecting the text in d1. Thanks in advance Nige -- Message posted from http://www.ExcelForum.com Hi try the following: - select cell D1 - goto 'Format - Conditional Format' - enter the following formula =(A1="blue")*(B1>1000)*(C1="horses") - choose a format -- Regards Frank Kabel Frankfurt, Germany > I am trying to format a cell...

Spell Numbers
-- After searching Google.groups.com and finding no answer, Bob Vance asked: I want to spell numbers from another cell eg: $99.00 = Ninety Nine Dollars No Cents Thanks in advance.........Bob Vance Hi the following would not do the 'no cents' part but as a beginning try: http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Regards Frank Kabel Frankfurt, Germany "Bob" <rjvance@ihug.co.nz> schrieb im Newsbeitrag news:OPZtDWnqEHA.2724@TK2MSFTNGP14.phx.gbl... > > > -- > After searching Google.groups.com and finding no answer, Bob Vance asked: > >...

Getting the line number after double clicking on CRichEditCtrl
I have one class class CBasicEdit : public CRichEditCtrl in which i am handling double click message. void CBasicEdit::OnLButtonDblClk(UINT nFlags, CPoint point) { int i = GetLineCount() ; CRichEditCtrl::OnLButtonDblClk(nFlags, point); } after double clicking i am getting the point where click happened. CPoint point. but how i will get the line number from this point. Thanks All now i am trying this but this is asserting Unhandled exception in basicedit.exe (RICHED20.DLL): 0xC0000005: Access violation void CBasicEdit::OnLButtonDblClk(UINT nFlags, CPoint point) { LONG i = Send...

convert numbers to dates
i need to convert large groups of numbers into dates automatically bu im stumped, the numbers are all in the format of 10.01.99 so they rea as dates but excel wont recognise them as dates is there a way t convert them all quickly to actual date -- jimbo69 ----------------------------------------------------------------------- jimbo693's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1421 View this thread: http://www.excelforum.com/showthread.php?threadid=26369 select the column of dates, then do Edit=>Replace Replace What . (period) Replace With \ -...

Format of downloaded data
> A data provider enables me to download real estate data that includes a column of sale prices in dollar amounts. > > The search through Internet explorer shows a page of data which can then be > downloaded to Excel. However the dollar items come into Excel as labels > rather than numbers which then prevents me from manipulating this data. > > This only happens when I download the data with my office desktop computer. > When I download it at home or on my laptop the data comes through as numbers > not labels. This suggests that I have a setting which is incor...

delimited file format issue
i get reports from (idx) a program that are delimited files. the columns are all fine but 1 and i can't figure it out. its supposed to be money and comes across as 11500. in the original program (idx) it would look like 115.00 and that is how i want it to look in excel. i tried the easy stuff like formatting and i keep getting 11500.00 or 1150.0 in my simple mind i thought it would be easy to move that decimal but its not turning out that way. i have thousands of line items so doing it one by one is not the optimum solution. any help is appreciated. excel 2003 os XP You really wa...

Format 2 colors
Using Formula =IF(T1399>2000000,"Heavy","Normal"), with conditional formatting I can color Heavy when it pertains, but I cannot color Normal when it applies. How can I have two colors depending on the True or False of the formula? Add a second condition, eg Condition 1: =AND(ISNUMBER(E1),E1>20) Format as desired Condition 2: =AND(E1<=20,E1<>"") Format as desired -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Old Red One" wrote: > Using Formula =IF(T1399>2000000,"Heavy","Normal"), ...

if statement to format cell
can anybody tell me how to fill a cell with color red if the value is negative and no fill if it is positive? thanks Hi Use Conditional formatting for this select the cell(s) you want this to happen in format / conditional formatting cell value is less than 0 click on the format button choose the patterns tab, choose red click OK & OK Regards JulieD "hovendick" <anonymous@discussions.microsoft.com> wrote in message news:0a5201c47afa$d97d0b80$a501280a@phx.gbl... > can anybody tell me how to fill a cell with color red if > the value is negative and no fill if it i...

Cell format, want 100 to display as 1.00
In my Excel 97, I can format cells such that a number entered as, for example, 100 becomes 1.00 when I hit enter. Now in my Excel 2000 I simply can't get it to do that anymore. Now when I enter 100 it turns into 100.00 and that is not what I want. I want ### to be #.## Not ###.00 How can I fix this? I have been through all the cell formatting options and It just won't do it. Any ideas? Thanks. -Dan :confused: -- gliebetronics ------------------------------------------------------------------------ gliebetronics's Profile: http://www.excelforum.com/member.php?action=get...

Replacing numbers
I have a spreadsheet which contains 6 digit numbers (i.e.209111, 209112, 209113). I would like to perform a bulk replace for just the first 3 numbers. Turn 209*** into 210***. Any suggestions? Thanks, Randy select the data>edit replace>209 >210 Just make SURE that 209 doesn't exist anywhere else -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Randy B." <RandyB@discussions.microsoft.com> wrote in message news:4C2D7690-17A3-4836-8978-A4840725CE1E@microsoft.com... >I have a spreadsheet which contains 6 digit numbers (i.e.209111,...

conditional formatting excel
I would appreciate some links on learning more about formulaes for conditional formatting - I find it very interesting and would like to know a wide range of things that it can do. thanks. S Try: http://www.contextures.com/xlCondFormat01.html for a starter... Bob Umlas Excel MVP "student" <lorenh@btinternet.com> wrote in message news:%23YR8LGslJHA.4564@TK2MSFTNGP06.phx.gbl... >I would appreciate some links on learning more about formulaes for >conditional formatting - I find it very interesting and would like to know >a wide range of things that it can do. ...

Conditional Average
Hi Everybody, I am trying to do a conditonal AVERAGE on an excel spreadsheet and a stuck...What I would like to do is check a one row for a certain value and if true, AVERAGE the corresponding values on another coumn. Is this possible? thanks. -me -- MEK91 ----------------------------------------------------------------------- MEK911's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2711 View this thread: http://www.excelforum.com/showthread.php?threadid=46628 Let's assume you have a value in cell E23 that you want to look for i Range G22:L22. If found,...

Time format for text box
I'm struggling abit here. I have the following formatting code for a couple of textboxes. But it's not working. Any suggestions? Private Sub UserForm_Initialize() TextBox78 = Format(Sheets("summary").Range("N21"), "hh:mm") TextBox79 = Format(Sheets("summary").Range("N22"), "hh:mm") End Sub Hi, That works fine but you have to have the time displayed in N21 and not just formatted to show the date. You can change the format of N21 & N22 at runtime if necessary to make it display the time Sheets(...