1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Conditional formatting - Two conditions... One format

Discussion in 'Business Applications' started by Blinken86, Mar 26, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Blinken86

    Blinken86 Thread Starter

    Joined:
    Mar 25, 2008
    Messages:
    9
    I need to conditionally format a cell based on the outcome of a formula and the value in another cell and I’m stuck as to how to do it.

    I have a table that has the next 50 weeks of our project across the top row (row 10) and subsequent "week ending" date in the next row (row 11) and two columns (columns I and J) that I enter the start and end dates in respectively (always a 5 day working week whose end date corresponds to that stated in row 11).

    For each row I have used conditional formatting based on the function (=AND(L$11>=$I14,L$11<=$J14)=TRUE) to highlight the cell on row 14 for which the dates I enter (I14 and J14) fall within the given week of the project based on the ending date of each week (row 11).

    This has worked perfectly as a scheduling tool up till now however I have been asked to change it. I now require that the formatted colour be one colour when the value in a separate column (column F) is 1 and another colour when the value in the column F is 2. Any help would be greatly appreciated.

    I have attached the document to help if needed and please post if it's not able to be done please.

    Thanks in advance...
     

    Attached Files:

  2. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    You can shorten your formula to,

    =(L$11>=$I14)*(L$11<=$J14)

    Do you want to add another condition? Or is this a separate issue with Column F?
     
  3. Blinken86

    Blinken86 Thread Starter

    Joined:
    Mar 25, 2008
    Messages:
    9
    Yes basically... I want the fuction to conditionally format a certain cell(s) on each row that corresponds to the date I enter in column "I" and duration i enter in column "H" (this automatically calculates column "J", the end date, as you've no doubt seen). This cell must be blue for when the value in column "F" is =2 and orange when the value in column "F" is =1. :confused:
     
  4. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    My understanding is you want to colour cells K14:BH34 (select these cells and add the conditional formats).

    If so the conditional formatting should be:
    Condition 1: =IF((L$11>=$I14)*(L$11<=$J14),IF($F14=1,TRUE))
    Format: ORANGE

    Condition 2: =IF((L$11>=$I14)*(L$11<=$J14),IF($F14=2,TRUE))
    Format: BLUE

    Sheet attached.

    Let me know if this is what you're looking for.
     

    Attached Files:

  5. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Just noticed that this is slightly off, and I think you're actually looking to start from Column K, not Column L; in which case it should be:

    Condition 1: =IF((K$11>=$I14)*(K$11<=$J14),IF($F14=1,TRUE))
    Format: ORANGE

    Condition 2: =IF((K$11>=$I14)*(K$11<=$J14),IF($F14=2,TRUE))
    Format: BLUE

    Reattached.
     

    Attached Files:

  6. Blinken86

    Blinken86 Thread Starter

    Joined:
    Mar 25, 2008
    Messages:
    9
    Thanks for that mate... mucho mucho appreciato'.

    I woke up last night with a flash of inspiration and thought of using =AND(L$11>=$I14,L$11<=$J14,$F14=1)=TRUE (Orange) and =AND(L$11>=$I14,L$11<=$J14,$F14=2)=TRUE (Blue) but your equations are a lot easier to check.

    It works great but I've run into another problem now (sorry to be a pain). I've changed the "Finish Date" column to show N/A if the values in the "Start Date" and "Duration" columns are equal to 0 (manager's request). I've used =IF(H14=0,"N/A",I14+(H14/5)*7-3) for the end date on row 14 for example.

    With this change i've noticed now that if the values in the kick-off Information cells (columns H,I and J) is equal to 0 then the whole row becomes highlighted. Is there a way around this so that if the information is =0 then the cells are left blank? Or should i suggest he just sucks it up and stop being such a pedantic princess about it?

    Unfortunately i can't just filter all the 0's away as for some unknown reason he also wants to see who isn't going on each trip - management quite hasn't worked out how to use the auto filter... :D

    Your on-going help is greatly appreciated!
     
  7. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    No problem - bosses hey!

    Conditions should be as follows:

    Condition 1: =IF($H14=0,TRUE)
    Format: (NONE)

    Condition 2: =IF((K$11>=$I14)*(K$11<=$J14),IF($F14=2,TRUE))
    Format: BLUE

    Condition 3: =IF((K$11>=$I14)*(K$11<=$J14),IF($F14=1,TRUE))
    Format: ORANGE

    - I would just copy and paste Condition 1 into Condition 3, and add the new condition into Condition 1. Its important that its in that order.
     
  8. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Actually, a better answer is:

    Condition 1: =IF($H14<>0,IF((K$11>=$I14)*(K$11<=$J14),IF($F14=1,TRUE)),FALSE)
    Format: ORANGE
    Condition 2: =IF($H14<>0,IF((K$11>=$I14)*(K$11<=$J14),IF($F14=2,TRUE)),FALSE)
    Format: BLUE
     
  9. Blinken86

    Blinken86 Thread Starter

    Joined:
    Mar 25, 2008
    Messages:
    9
    Perfect! So simple really. This is what happens when you're given too much work... You forget to use your head...

    Cheers heaps mate! Once again appreciate your help with this. Your a champ. (y)

    Now back to scheduling... :mad:
     
  10. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    And you can use "H" or change to "I" if you'd rather base it on that Column; or if you want to base it on "J" then you also need to replace "<>0" with "<>"N/A".
     
  11. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    :D - No problem, I've had to Scheduling like this in the past.

    Ps. Don't forget to add "Solved:" to the title of your original post.
     
  12. Blinken86

    Blinken86 Thread Starter

    Joined:
    Mar 25, 2008
    Messages:
    9
    Your right actually. This is a very handy addendum.

    Helps a lot considering our billion dollar company doesn't want to upgrade to the new microsoft office so excel is still limited to three conditional formats.

    Gotta love the new excel... filtering by color... 30ish conditional formats. :p So good for scheduling purposes.

    Big help mate! (y) And it should be showing it as solved now...
     
  13. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Well I'll let you get back to work.

    Colour filtering is a great new addition, have had to write a macro to filter by colour in Excel 2003, as my £ Billion company hasn't upgraded to 2007 yet either.
     
  14. Blinken86

    Blinken86 Thread Starter

    Joined:
    Mar 25, 2008
    Messages:
    9
    I can never figure it out... Mhe... i've just got paid for finding this solution so if their happy to pay for it then let them stick with 03'.

    Once again a great help and hopefully someone else gets some help from this...
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Conditional formatting
  1. Squashman
    Replies:
    5
    Views:
    358
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/697157

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice