Conditional formatting if condition of an if statement is true and a string value is displayed

Dear Experts:

I got a nested 'IF'-Formula in an excel cell that returns nothing if
the condition is false.

I would like to conditionally format this cell with grey shading if
the condition is true and a string value is displayed.

How can this be achieved using excel conditional formating
functionality ?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
andreashermle
5/26/2010 3:55:45 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
4110 Views

Similar Articles

[PageSpeed] 10

Let's say your doing this conditional formatting in cell K17. In
conditional formatting (xl2003) choose the FormulaIs: option and enter:
=$K$17<>""
choose your formatting and OK.

In xl2007, choose NewRule..., then 'Use a formula to determine which
cells to format' and in the 'Format values where this formula is true'
field, use the formula given above, set your format and OK.
Make sure there are no other conditional formats interfering in the
same cell.




andreashermle;731842 Wrote: 
> 
Dear Experts:
> 
> I got a nested 'IF'-Formula in an excel cell that returns nothing if
> the condition is false.
> 
> I would like to conditionally format this cell with grey shading if
> the condition is true and a string value is displayed.
> 
> How can this be achieved using excel conditional formating
> functionality ?
> 
> Help is much appreciated. Thank you very much in advance.
> 
> Regards, Andreas


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=205130

http://www.thecodecage.com/forumz

0
p45cal
5/26/2010 9:23:24 PM
Hi

Select the cell, in my example I will use A1, goto conditional
formatting > Condition1: Formula is > enter this formula:

=A1<>""

Select the desired formatting.

Hopes this helps.
....
Per

On 26 Maj, 17:55, andreashermle <andreas.her...@gmx.de> wrote:
> Dear Experts:
>
> I got a nested 'IF'-Formula in an excel cell that returns nothing if
> the condition is false.
>
> I would like to conditionally format this cell with grey shading if
> the condition is true and a string value is displayed.
>
> How can this be achieved using excel conditional formating
> functionality ?
>
> Help is much appreciated. Thank you very much in advance.
>
> Regards, Andreas

0
Per
5/26/2010 9:44:39 PM
Reply:

Similar Artilces:

A formula that displays the last letter in a text
I frequently use text in cells. I use MID and LEN often enough, but is there a formula that will display the last letter in a text that will often vary in length. Thanks. Michael =RIGHT(A1,1) -- __________________________________ HTH Bob <MichaelRLanier@gmail.com> wrote in message news:0983e9df-8991-43ff-ab3a-09ee5450fbd6@a70g2000hsh.googlegroups.com... >I frequently use text in cells. I use MID and LEN often enough, but > is there a formula that will display the last letter in a text that > will often vary in length. Thanks. > > Michael Hi Michael =MID(A1,...

"conditional formatting" "#values"
I have a formula that works fine but... some of the cells show #values, what i would like to do is conditional formatting them to white font if = #values. Any pointers? many thanks Try this: Select the cells to be impacted (I'll assume A1:A10, with A1 as the active cell) From the Excel main menu: <format><conditional formatting> Condition_1 Formula is: =ISERROR(A1) Click the [Formatting] button.....set the White font....Click the [OK] buttons. Does that help? *********** Regards, Ron XL2002, WinXP "Dewi..." wrote: > I have a formula that works fine bu...

Conditional Formatting question...
Is there a way to have Excel look in a particular cell, and if the value in the cell meets a requirement (such as Complete), then shade the entire row a different color? I can use conditional formatting to get the one cell shaded, but I can't get the rest of the row to be shaded. Thanks! Dino Try using an absolute reference in your Conditional Format formula. For example, if the cell that contains the value "complete" is A1, then refer to it as $A$1. "Dino" wrote: > Is there a way to have Excel look in a particular cell, and if the value in > the ce...

RFX_Text converts empty strings to NULL when using ODBC and MFC
When submitting updates to the database and the column has a Not NULL constraint I get an error from the ODBC driver. It appears that the field exchange mechanism is converting the empty strings to NULL. I have seen this topic posted before, however, the solution was never presented with the post. Thanks:-) ...

using sumproduct with a substring condition?
I have a calculation I just made that looks like this: =SUMPRODUCT(--(C5:C50="persons name"),I5:I50) which works out the hours billed from column I and works fine. Column E has a description in it which sometimes has at the end of the description the words "do not bill." Is there any way to add this condition to the sumproduct - i.e. exclude from the sumproduct any value in column I that has the "do not bill" sub-text in column e? Thanks, Ben >exclude from the sumproduct any value >in column I that has the "do not bill" Try it ...

Filter for Same Value in Two Fields
There are two fields in my query. "From" and "To". For sake of ease, let's say both these fields are US Cities. How can I pull all the records that show shipped "From" Denver and that show shipped "To" Denver? Thank you in advance. You stated: shipped "From" Denver and that show shipped "To" Denver Technically this would mean both values were Denver which might not make much sense if you expected the From and To to be different. Assuming you are viewing your query in design view, you would type "Denver" und...

Independently Formatting Columns and Rows
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is there a way to independently format the width of a column on specific cells? In other words, if I wanted to change the width of the cells in the middle of the document, can I do so without affecting the width of the cells above it and below it? On 2010-05-28 12:40:38 -0400, monks617@officeformac.com said: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Is there a way to independently format the width of a column on > specific cells? In other words, if I want...

Time format as hh:mm:ss
When inputing time as above, Excel is thinking it is a Time as AM or PM, but actually it is a lapsed time. How do I get it to do just hh:mm:ss? Without placing in the : each time would be a timesaver as well. Regards, -- Dennis Hi Dennis, Choose another date format from the Format>Cells>Number tab>Time list. If there isn't any without AM/PM, use a Custom format and format as hh:mm:ss (or choose other time settings in Windows International) Actually Excel doesn't know the difference between elapsed time and a point in time. Technically there is no difference; a point...

formatting or moving a radar chart's axis labels
I am attempting to change the orientation of a radar chart's axis labels. Its orientation seems locked in place immediately to the left of the 360=B0 gridline. I would like the ability to display the axis labels directly over the range rings and along other gridlines, such as 180=B0 or 270=B0. With other charts, the formatting option allows one to select "high", "low", or "next to axis" to change the position of the axis. This maneuver seems to have no affect whatsoever on a radar chart. As a final resort, I suppose I could turn the axis labels off...

display bug
Dear all, I have a strange display bug in access 2007. I have a continuous form, displaying, for each record: 4 comboboxes bound to a field an unbound icon a label with a controlSource ( =trim([field1]) & " " trim([field2]) ) a bound text field Initially, 1st combobox is empty for all record. Initial display is right. There is no conditional formatting. This error only occurs in acces 2007, not in other versions. There are no events on form update There are events onBeforeUpdate, onAfterUpdate, onEnter on first combobox. Strange thing 1: on each click, "calculating ...

SUM for logical values
Using Excel 2002 SP3... If I type logical values into the formula, like this: =SUM(TRUE, TRUE, TRUE), my formula returns the value 3. If I reference cells containing logical values, like this =SUM(A1:A3), my formula returns 0. Does anybody know why there is a difference? -- tj Try: =A1+A2+A3 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tjtjjtjt" <tjtjjtjt@discussions.microsoft.c...

How to Enable merge and center icon on the formating toolbar?
In the formating toolbar Merge and Center Icon is in disable condition. So that i could not merge the required cells which i want. Could u please help me in this issue to enabling that icon in the formating toolbar. Jyothi, Unprotect the worksheet, and that button will become available. HTH, Bernie MS Excel MVP "Jyothi" <Jyothi@discussions.microsoft.com> wrote in message news:A348F3C1-8D49-495C-BCEE-9C0DAF6BF6AA@microsoft.com... > In the formating toolbar Merge and Center Icon is in disable condition. So > that i could not merge the required cells which i want. >...

biweekly formatting
Is there a way to format a date to be a unique format for biweekly dates I don't any other replies yet so try this If you have the date in A1 =IF(ISEVEN(A1-WEEKDAY(A1,1)+6),A1-WEEKDAY(A1,1)+6,A1- WEEKDAY(A1,1)+6-7) will return a biweekly Friday =A1=B1 in C1 returns true if A1 is a bi-weekly friday Conditional format A1 based on c1 = "true" To change the day, alter the +6 to +1 for Sunday through +7 for Saturday If you get the wrong week, change Iseven to Isodd I hope to see a more elegant solution posted. Because of circular logic I can't this formula into conditional for...

Using an IF statement to display an image ...
Hi all, I am using the following IF statement to show the % increase in costs, only if it is 10% or higher than the running average. If not the cell stays blank. =IF(AND(J11>10%,J11<>""),J11,"") Now .... could anybody tell me how I could change the above formula (or write some code) so that instead of showing the % increase from cell J11, it will display a picture of an unhappy face? Sounds a bit daft but it does capture peoples attention! I have managed to get something similar using the wingdings font (JKL) but it's not that great. I already have an emoti...

PDF Format for Excel
Can Excel spreadsheets be save in PDF format? Thom, Excel can't save files directly to PDF files. You need a third party product like Adobe's Acrobat (full version, not just the free Reader), or something like PDF995 (www.pdf995.com) . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Thom" <thancox@lgbcpa.com> wrote in message news:090401c3cfb4$a5eef630$a501280a@phx.gbl... > Can Excel spreadsheets be save in PDF format? Thom, Yes, but you need a program capable of doing so... A popular one is Adobe Acrob...

Array Constant: How do I reference each value in a formula
I have an array constant {1000,1.2,2%} in cell A1 S = 1000 P = 1.2 R = 2% How do I reference the array constant to create this formula: =(S/P)-((S/P)*R) Hi, I can't duplicate your entry. First, to be an array it must be entered starting with an = for example ={1,2,3} Second, you show the last argument as 2% - Excel won't accept the % in and array entry. Maybe you are typing {1000,1.2,2%} into the cell? If so, it is not an array, its just a bunch of text. You could enter it as ={1000,1.2,0.02} -- Thanks, Shane Devenshire "notso" wrote: > I have an array co...

How do I clear the formating of the area OUTSIDE my chart on my w.
...

Prevent cells with no value from being plotted on chart
I have a chart based on cells that are the result of a calculation. The cells show empty if the data used for the calculation is not yet available (future data). As a result, as my chart shows the whole year, the line drops and remains to zero after the last available set of data. Excel will let me prevent an empty cell of being plotted but since there is a fornmula is the cell, it is not considered empty, and therefore displayed. How can I prevent these cells to be plotted on the chart? Thanks Vince You can use an IF formula with NA(). For example: =IF(B2="",NA(),B...

How do I format text as a bulleted item in Excel? Is it possible?
It's a manual effort. You can use Insert|Symbol (if you're using xl2002+) or copy the bullet you want from CharMap. (Windows start button|charmap). You'll have to select that character in the formulabar and change the font to what you want. Chip Pearson has an addin that may help (if you're using xl2k or lower). http://www.cpearson.com/excel/download.htm (look for Symbolizer and make sure you get the correct version) DRB wrote: -- Dave Peterson Dave Peterson <petersod@verizonXSPAM.net> wrote in news:43062E62.984DE524@verizonXSPAM.net: > Or with: http://...

Disabling tables formatting features in Word2000 and Word2003
How can I disable the automatic table numbering and indenting features in MS Word? Jim Hawkins By asking in a Word newsgroup, not Windows XP. "Jim Hawkins" <jimhawkins@manx.net> wrote in message news:uRjHU6eELHA.588@TK2MSFTNGP06.phx.gbl... : : How can I disable the automatic table numbering and indenting : features in MS Word? : : Jim Hawkins : : : Bickford Shmeckler wrote: > By asking in a Word newsgroup, not Windows XP. > Such as ? I could only find 3 newsgroups about Word, one in hebrew, one in arabic and a third in a l...

merge multilple data values
how would I merge multilple data values: in seperate columns as: js-1234 to .jpg Thanks Hi Jennifer, ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Hi Jennifer, Try using =(A1&B1) where A1 and B1 are the cells where your data sits ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ X-No-Archive: yes "Jennifer Burnel" <jenny@...

Displaying html
Hi, I am not sure if this is possible. I want a component that can display Html on a UserForm. I think there are some components that will display HTML if you supply them with the source path for the html file. However, what I am looking for is a component that can take a String as an argument. If the String contains Html tags the component will format these tags into its visual represenation. for example: HtmlEditor.text= "<html><body><b>hello</b></body></html> Is this possible? ...

Qry-Statements
I want my report to show if my product is (specked) or (non-specked) In the query i am able to get only one of them to show up. I have tried use OR, and AND. They still only show one. I have created two drop down's on the form and have tried combinding them and that does not work. Can anyone help. -- Lisa S. Lisa We aren't there. We can't see what you are looking at. We have no idea what data you are storing in your tables, nor how you are querying them. More info, please! Regards Jeff Boyce Microsoft Office/Access MVP "Lisa" <Lisa@discussions.microsoft.co...

outlook cannot display embedded picture
My company's outlook 2003 suddendly can not display the embedded picture correctly, it all show as red cross. even view it in the internet zone. anyone can help urgently ? thanks Steven Might try clearing Outlook 2003's secure temp folder. This folder is stored under the "Temporary Internet Files" folder tree, so it can't be browsed to by using Windows Explorer. I tend to use a command prompt to get to said folder and clear it out using the erase command. And here are the steps if the above doesn't make sense. (Assuming you are using Windows XP) 1) Sel...

PJS2003
We have a couple plans that have their actual work values matching in the portfolio analyzer view. They stopped updating. They seem to be plans that were "saved as" another plan and then republished under the new name. Once the "Save as" another plan was completed, the olap work value stayed the same as the original plan's work value and did not continue updating with actual work reported. Is there a way to detached the "saved as" plan from it's original values in the OLAP cube? Example Project A has 5 hours total in Work on the Portfo...