+ Reply to Thread
Results 1 to 9 of 9

Conditional Formatting formula not working

  1. #1
    Registered User
    Join Date
    07-06-2018
    Location
    Kiev,Ukraine
    MS-Off Ver
    2016
    Posts
    8

    Conditional Formatting formula not working

    I am trying to create a calendar. I am trying to use a conditional format to "fill in" the dates where trips are scheduled. I will eventually extend the formatting to include all trip segments, but I am having trouble getting the first one to work. I wrote
    ="And(CD$3>=$C4,CD$3<=$D4)"
    and want it to format the cell with a fill color if true but it isn't working.
    I am new to conditional formats so I appreciate the help!
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Conditional Formatting formula not working

    formula was put as text
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-06-2018
    Location
    Kiev,Ukraine
    MS-Off Ver
    2016
    Posts
    8

    Re: Conditional Formatting formula not working

    Thank you. I can't believe I missed that. I was writing the formula in another place and copying it over. Thank you!

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Conditional Formatting formula not working

    Could you provide a bit more detail on the expected end result before you start extending your formula.

    For what it's worth now, I was able to shorten your formula in CD4 to

    =IFERROR(LOOKUP(SUMIFS($C$2:$CA$2,$C$3:$CA$3,"Start Date",$C4:$CA4,"<="&CD$3,$D4:$CB4,">="&CD$3),$E$2:$CC$2,$B4:$AZ4),"")

    While still getting the same results. We might be able to help you with something similar for your conditional formatting rather than you making another long formula with hundreds of AND() conditions.

    Any reason for merging cells in some places, for example DG4:DK4? It might look neater, but if the dates get changed it will all go wrong

  5. #5
    Registered User
    Join Date
    07-06-2018
    Location
    Kiev,Ukraine
    MS-Off Ver
    2016
    Posts
    8

    Re: Conditional Formatting formula not working

    Jason - thanks so much for the input. What I am trying to do is create a "calendar" that will shade in the dates for trips and label the trip.

    It was working but labeling each day in the trip - that is why I tried merging the cells but that was a manual fix and would require the user to always manually modify (unmerge, recopy the formulas
    if dates were changed, as you saw.

    So then I tried to use a conditional format to highlight the cells instead. That works but I will need to extend the formula to allow for all 20 potential trip segments (right now it is only set for trip segment 1).
    I changed my cell formula to only write the trip location on the start date and leave the other days in the trip "" but they aren't blank as the label isn't allowed to spillover into the adjacent cells.

    sigh.... now I am just so tired and frustrated that I am on the verge of giving up.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Conditional Formatting formula not working

    Quote Originally Posted by panama74 View Post
    I changed my cell formula to only write the trip location on the start date and leave the other days in the trip "" but they aren't blank as the label isn't allowed to spillover into the adjacent cells.
    To clarify that, for what little it is worth in this instance, they are blank, but they are not empty. In some situations excel treats both the same, in others it does not. Unfortunately this is one where it does not, otherwise the ideal suggestion would be to do what you tried then highlight the duration with conditional formatting.

    Don't give up just yet, I'll give it some thought, see if I can come up with something different.

  7. #7
    Registered User
    Join Date
    07-06-2018
    Location
    Kiev,Ukraine
    MS-Off Ver
    2016
    Posts
    8

    Re: Conditional Formatting formula not working

    Thank you Jason! You've given me some confidence that I am not completely hopeless at this

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Conditional Formatting formula not working

    Hi, just taking another look at this, I noticed that you have what appears to be a sub-heading of 'Residents:' in A10 of the calendar sheet.

    Is there anything in the original data that will enable the identification of Advisors that should fall under this subheading?

    Where the end date is N/A should that trip be entered on the calendar from the start date all the way to the end of the calendar, or for a shorter period?

  9. #9
    Registered User
    Join Date
    07-06-2018
    Location
    Kiev,Ukraine
    MS-Off Ver
    2016
    Posts
    8

    Re: Conditional Formatting formula not working

    The sub-headings of resident (and intermittent) can be ignored and deleted. There is nothing in the original data tab that enables that identification but it is really not important. As for the N\A date , I think that shoujd be a error value - I was going to put a data validation rule on that column to require a valid end date. These are all distinct trips so that shouldn’t be a logical rule (that is, if they do not know the end date yet, my directions will be to not enter the trio until they do)

    Thanks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting not working on cell that has formula
    By ely.rodriguez in forum Excel General
    Replies: 8
    Last Post: 09-26-2017, 02:16 PM
  2. =if(and(... formula in conditional formatting dialogue box not working...
    By ChrisCD in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-27-2016, 02:22 AM
  3. [SOLVED] Conditional Formatting Not Working With Formula
    By markd038 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-28-2014, 04:05 AM
  4. Conditional Formatting Formula Not Working Properly
    By Oscar Martin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-23-2014, 08:59 AM
  5. AND in Conditional Formatting Formula not working
    By readyemail in forum Excel General
    Replies: 11
    Last Post: 11-30-2010, 06:00 PM
  6. using a formula in conditional formatting not working
    By missmischa in forum Excel General
    Replies: 4
    Last Post: 02-22-2010, 06:04 PM
  7. Conditional Formatting with LARGE formula not working
    By peri1224 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-02-2010, 09:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1