• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting

E

Ehyman91

New Member
Hello folks,
I have a problem with conditional formatting and I need some help.
I have set up some rules to highlight certain cells if a value falls in or outside of another range. The ranges are defined on different tabs (based on dates and percentages) within the same spreadsheet. The current formatting has worked fine until now but as I'm looking to introduce another layer the new rule falls down.
My issue is:
If on the Menu tab (cell W12) the value is greater than 0 I want it to be highlighted green only if the value in cell G3 (on the Data tab) is blank.
Options tried:
I have tried using the following but it didn't help overall.
=AND(Menu!W12>0, ‘Data’!G3=” “) for the following cells =$W$12:$W$24

Thanking you in advance
E
 

Attachments

  • Conditional Formatting.xlsx
    130.6 KB · Views: 9
Select W12:W24

Edit the CF and edit the green (2nd CF)
Use a Formula =AND($W12>0, Data!$G$3="")

Type that to ensure the " are the correct " 's

Set the CF to stop if True
 
I also encountered problems because unwanted values of W12 etc are not 0s. The formula
=(W12:W24<>"")*(Data!$G$3=" ")
worked better for me.

In looking through, I also found a formula
Code: 
=IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M15,'T2 - 2021'!$M$1<='T2 - 2021'!B15),0.05,IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M20,'T2 - 2021'!$M$1<='T2 - 2021'!B20),0.15,IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M27,'T2 - 2021'!$M$1<='T2 - 2021'!B27),0.25,IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M33,'T2 - 2021'!$M$1<='T2 - 2021'!B33),0.3,IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M38,'T2 - 2021'!$M$1<='T2 - 2021'!B38),0.4,IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M41,'T2 - 2021'!$M$1<='T2 - 2021'!B41),0.5,IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M44,'T2 - 2021'!$M$1<='T2 - 2021'!B44),0.6,IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M47,'T2 - 2021'!$M$1<='T2 - 2021'!B47),0.7,IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M51,'T2 - 2021'!$M$1<='T2 - 2021'!B51),0.8,IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M54,'T2 - 2021'!$M$1<='T2 - 2021'!B54),0.9,IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M58,'T2 - 2021'!$M$1<='T2 - 2021'!B58),0.98,IF(AND('T2 - 2021'!$M$1>='T2 - 2021'!M62,'T2 - 2021'!$M$1<='T2 - 2021'!B62),1))))))))))))
I think it might be possible to use something of the form
Code: 
= SUM(XLOOKUP(today, WeekCommencing, Percentages, ,-1))

or

= SUM(INDEX(Percentages, MATCH(today, WeekCommencing), {1,2,3}))
instead.
 
Thank you lads.
Peter, I need more time to look at your suggestions regarding my weekly milestone formula but I appreciate the feedback.
However, in terms of the original question Hui's recommendation above works best. Peter, I can't get your formula to work at all.

Next issue is...
I've now fast forwarded the week from =TODAY() to 15/12/2020 on the "T2 - 2021" tab (cell M1) and input some data on the "Staffing Levels" tab.
With the current Conditional Formatting ideally I should now see cell W12 on the "Menu" tab highlighted in green and cell W13 should remain white. Sadly they're both white.

Any more advice?
 

Attachments

  • Conditional Formatting.xlsx
    130.3 KB · Views: 1
Last edited:
Of course, the condition Data!$G$3="" is not fulfilled, so the CF is not triggered

BTW your formula in Data!$G$3 contains a lot of unnecessary AND functions
You would be much better off creating a small table somewhere with two columns, the first containing possible values for F3 (100%,98%,...) and the second with the value to be subtracted from F3.0.01 0.07,...
Then you would obtain a short formula like =IFERROR(F3-Vlookup(F3,range_of_small_table,2,0),"")
 
Sorry. I had got completely mesmerised by the horror of the formula in Data!$F$3 (and Data!$G$3) and lost concentration when it came to the conditional formatting issue. For simplicity, I develop all formulas as array formulas and I forgot to break the formula down to single cell relative referencing that is needed for conditional formatting.
=(W12<>"")*(Data!$G$3<>"")
It appeared to me that the numbers you return in cell Data!$F$3 might be related to the table on 'T2 - 2021'! and permit some form of lookup, as suggested by @pecoflyer.
 

Attachments

  • Conditional Formatting (1).xlsx
    132.8 KB · Views: 7
To use conditional formatting in excel follow these steps
  • Select the desired cells for the conditional formatting rule.
  • From the Home tab, click the Conditional Formatting command. A drop-down menu will appear.
  • Hover the mouse over the desired conditional formatting type, then select the desired rule from the menu that appears.
  • A dialog box will appear. Enter the desired value(s) into the blank field.
  • Select a formatting style from the drop-down menu. In our example, we'll choose Green Fill with Dark Green Text, then click OK.
  • The conditional formatting will be applied to the selected cells.

Regards,
Rick Bale
 
You must log in or register to reply here.
Back
Top