tag:blogger.com,1999:blog-1869924468172210809.post6630902947910522355..comments2021-06-13T10:17:15.193+01:00Comments on Not Just Numbers: Excel Tip: Conditional Formatting based on other cellsGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger52125tag:blogger.com,1999:blog-1869924468172210809.post-26327612197536645842021-01-24T21:00:24.790+00:002021-01-24T21:00:24.790+00:00Thank you so much!Thank you so much!Cassiehttps://www.blogger.com/profile/06627726673245566405noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-52238358982155969762021-01-24T05:22:14.837+00:002021-01-24T05:22:14.837+00:00Cassie
I don't think it's conditional for...Cassie<br /><br />I don't think it's conditional formatting you want. You need an IF formula in Column E itself.<br /><br />If you try (in cell E14):<br /><br />=IF(B14<>"",-D14,D14)<br /><br />That's if you want the positive value of thev rder if the x isn't there. Otherwise change that last argument to whatever you do want when there's no x.<br /><br />I wrote a post on the IF statement at:<br /><br />https://www.notjustnumbers.co.uk/2010/10/excel-tip-if-statement-made-simple.html<br /><br />I hope that helps.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-17106934355938220302021-01-24T00:14:11.666+00:002021-01-24T00:14:11.666+00:00I need help. I'm trying to make my cell popula...I need help. I'm trying to make my cell populate with a negative dollar amount based on if there is an "x" in another cell.<br /><br />Column B is empty. Column C is name. Column D is total cost of the order. If an order is received online then "X" is entered in column B. If the "X" is present. I need column E to be the negative number from column E. The cash is never seen in hand and we don't want to count it.<br /><br />So far I have =$B14<>""<br /><br />What I have may be wrong but it is the only way I know to indicate that the "x" needs to be there for something to happen. I have no idea what to do now though.Cassiehttps://www.blogger.com/profile/06627726673245566405noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-73612410614415854632017-01-30T06:31:55.754+00:002017-01-30T06:31:55.754+00:00I'm not clear on what you want. Are you saying...I'm not clear on what you want. Are you saying that you want it to highlight red if it's less than 10, or that it is and you don't want it to.<br /><br />If you want it to, then change the rule to<br /><br />=OR(A2>1000,A2<10)<br /><br />If you don't want it to, that would suggest that there is another rule being applied, so click Manage Rules, and remove the offending rule. Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-79639636930049564492017-01-30T06:26:07.653+00:002017-01-30T06:26:07.653+00:00Ty
If the first cell in the range want to format ...Ty<br /><br />If the first cell in the range want to format is C2, then highlight the whole range that you want to format and use the following formula and add rule. Use the following formula in the box:<br /><br />=ABS(C2-C1)=2500<br /><br />and set your red format when you hit the format button.<br /><br />The ABS function ignores the sign, and it is important that you don't have the dollar signs on the references so that they will apply relatively over the whole range.<br /><br />Then just add anothe rile for each of the other deltas.<br /><br />I hope that helps.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-19747788658163050072017-01-30T00:49:20.223+00:002017-01-30T00:49:20.223+00:00I am having trouble coming up with a proper condit...I am having trouble coming up with a proper conditional formatting rule for a dataset. I am running a general ledger monthly report and I am looking to make it easier to identify values that change month to month by a certain dollar amount (250, 500, 1000, 2500). So in essence I want to highlight a cell red if the difference between its value and the previous cell is 2500 (either plus or minus). Then I want to do the same for the other three deltas. And to make matters worse I want to be able to copy that formula across many cells while referencing each cell's adjacent/previous cell. <br /><br />Please help and thank you. I am trying to get something done for tomorrow.Tyhttps://www.blogger.com/profile/04282720103829035613noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-44180941417670698892017-01-29T12:51:53.550+00:002017-01-29T12:51:53.550+00:00I'm trying to create a results spreadsheet in ...I'm trying to create a results spreadsheet in excel that in one columb where the results is higher than 1000 it highlights red, which i have done, however the result can be <10, if this is the case this cell highlights in red as well. How to i prevent this? Any help greatfully appreciatedAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-60708936726137513082016-11-10T14:31:55.136+00:002016-11-10T14:31:55.136+00:00You don't add them at the same time. Just add ...You don't add them at the same time. Just add one rule completely, then add a new rule in exactly the same way.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-54629118984089747352016-11-10T14:08:22.485+00:002016-11-10T14:08:22.485+00:00I am lost! I can only apply one conditional format...I am lost! I can only apply one conditional format per time. How can apply four at the same time?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-91392344265770673062016-11-10T13:55:35.721+00:002016-11-10T13:55:35.721+00:00It doesn't matter what's in the other cell...It doesn't matter what's in the other cells. If a cell doesn't meet any of the four criteria, its format won't change.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-14339590590161223602016-11-10T13:42:51.393+00:002016-11-10T13:42:51.393+00:00How do i do that when its a full column of items t...How do i do that when its a full column of items that have different result from the list of 4?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-41100426034556504202016-11-10T12:16:07.972+00:002016-11-10T12:16:07.972+00:00You won't need a formula for that in the condi...You won't need a formula for that in the conditional formatting. Just apply four separate conditional formats to the cell, e.g.<br /><br />Cell contents equal to Valid, format as green<br /><br />Do the same for each of the four formats.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-33555896755063701822016-11-10T09:01:55.815+00:002016-11-10T09:01:55.815+00:00Hi, I am trying to apply conditional formatting to...Hi, I am trying to apply conditional formatting to a a cell that contains this IF(D6="Not Applicable", "Does Not Expire", IF(D6="","Pending", IF(D6>TODAY(), "Valid", IF(D6<=TODAY(),"Expired")))) and i would like to format as follows: Valid = Green, Expired = Red, Pending = Amber and Does Not Expire = Blue...How can i do it?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-40025351422998681252016-10-29T07:05:05.689+01:002016-10-29T07:05:05.689+01:00Just apply 2 different conditional format rules to...Just apply 2 different conditional format rules to cell G3.<br />Condition 1:<br />=AND($C$3="yes",$G$3>=15)<br />Condition 2:<br />=AND($C$3="no",$G$3>=30)<br />Choosing your required formats for each.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-70018935794044954782016-10-29T01:05:17.115+01:002016-10-29T01:05:17.115+01:00Regarding conditional formatting... If C3 is yes A...Regarding conditional formatting... If C3 is yes AND G3 is =>15 the format would turn the number(in G3) BOLD/red. Then IF C3 is no AND G3 =>30 turn the number (in G3) Bold/green.Anonymoushttps://www.blogger.com/profile/15267122266337689212noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-79353127663011456262016-08-17T16:50:51.747+01:002016-08-17T16:50:51.747+01:00Ah you are a lifesaver!! Thank you! Ah you are a lifesaver!! Thank you! Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-82183491939725511642016-08-17T15:02:00.385+01:002016-08-17T15:02:00.385+01:00Again, if the value was in A1:
=AND(MOD(A1,1)>...Again, if the value was in A1:<br /><br />=AND(MOD(A1,1)>=0.5,MOD(A1,1)<=0.7)<br /><br />should do it.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-69134979790614342342016-08-17T14:27:05.619+01:002016-08-17T14:27:05.619+01:00This is great thanks! One question though, if I wa...This is great thanks! One question though, if I wanted to conditionally format the cell based on the value after a decimal point, how would I do that?<br /><br />i.e. - if the value in the cell is 34.60 and I wanted to colour any cell red where the number after the decimal is within the range 50 to 70. Thanks.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-82022355348326302582016-08-17T10:36:55.420+01:002016-08-17T10:36:55.420+01:00It works! Thank you! You've been a big help!It works! Thank you! You've been a big help!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-31624581642791791062016-08-16T16:02:56.917+01:002016-08-16T16:02:56.917+01:00The easiest way to do that is just format them as ...The easiest way to do that is just format them as clear normally. The conditional format will only kick in if the dropdown does equal "Non-beneficial"Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-26822262364891610272016-08-16T15:43:22.036+01:002016-08-16T15:43:22.036+01:00That's great thank you! Can I possibly add a t...That's great thank you! Can I possibly add a tiny bit to the formula that means if anything other than "Non-beneficial" is selected from the drop down box the cells will all stay clear?<br />Thanks,<br />BeckyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-5908604018146447612016-08-16T14:22:34.768+01:002016-08-16T14:22:34.768+01:00Yes, just follow the steps above.
If the cell con...Yes, just follow the steps above.<br /><br />If the cell containing the dropdown is A1, then the formula to use in the conditional formatting is:<br /><br />=$A$1="Non-beneficial"Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-39578392850585555892016-08-16T14:22:22.828+01:002016-08-16T14:22:22.828+01:00Yes, just follow the steps above.
If the cell con...Yes, just follow the steps above.<br /><br />If the cell containing the dropdown is A1, then the formula to use in the conditional formatting is:<br /><br />=$A$1="Non-beneficial"Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-84081627012541117452016-08-16T13:51:41.493+01:002016-08-16T13:51:41.493+01:00Hi,
Is it possible to add conditional formatting t...Hi,<br />Is it possible to add conditional formatting to shade a range of cells grey if one cell (which already contains a Data validation list and is a merged cell) says "Non-beneficial" (non-beneficial is one of the options available in the drop down list)?<br />Thanks,<br />BeckyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-23800773540711756182016-07-02T07:52:20.409+01:002016-07-02T07:52:20.409+01:00Great. It's just a specific application of thi...Great. It's just a specific application of this post, but a one that people want to do a lot.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.com