+ Reply to Thread
Results 1 to 6 of 6

IF THEN or Conditional Formatting question

  1. #1
    Registered User
    Join Date
    07-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    IF THEN or Conditional Formatting question

    Excel 2010

    I'm making a log for parachute equipment. Essentially I have 3 columns, "CANOPY EXPIRATION" (column C), "HARNESS EXPIRATION" (D), "RISER EXPIRATION" (E). Right now those cells for each chute ID are conditionally formatted to change color based on expiration date. For example, I have it set to
    Turn red when cell value is between =TODAY() and (TODAY()-99999) (showing expired equipment)
    Turn yellow when cell value is between =TODAY() and (TODAY()-180) (identifing 180 days until expiration)
    Turn green when cell value is between =TODAY() and (TODAY()-365) (identifing 365 days until expiration)

    After those 3 columns, I have a 4th column titled "FLYABLE?" (column F). This column I want to be a simple Y or N and color coded green when Y, red when N.

    I am having trouble setting this column up to read from the other 3 colums though. I want the value of this column to detect if any of the other 3 columns are expired, and then display the applicable Y or N and color code.

    I have tried inputting this into cell F4: =IF((C4:E4)<TODAY(),"N","Y")
    I thought that would have read from cells C4 through E4, determine if any are less than today, then display "N" if true, "Y" if false. However, I get an error #VALUE!. In evaluating the formula, it underlines the (C4:E4) portion, so I assume that is where it is detecting the error. I'm not sure what's wrong with that though.

    Besides that, is it even possible to have a cell change color based on an "=IF" ? Is there an easier way to do this with conditional formatting?

    I'm pretty new to this, just learned some basic conditional formatting today while building this spreadsheet.

    I have attached the file .xlsx and .xls, but the .xls may not have all the functionality.
    Attached Files Attached Files

  2. #2
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: IF THEN or Conditional Formatting question

    Rivanthe5th

    In Cell F4 and drag down
    =IF(OR(C4<TODAY(),D4<TODAY(),E4<TODAY()),"N","Y")

    And CF: =$F4="N"
    CF: =$F4="Y"
    Apply formatting

  3. #3
    Registered User
    Join Date
    07-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: IF THEN or Conditional Formatting question

    I had some conditional formatting wrong before, should have been yellow when =TODAY()+180 and green when =TODAY()+365

    As for the formula you gave me, I applied =IF(OR(C4<TODAY(),D4<TODAY(),E4<TODAY()),"N","Y") to F4, and the cell stated "TRUE". However, the harness is expired, so it should be false. Perhaps this is due to my editing the conditional formatting. Also, I'm not sure where to put the

    CF: =$F4="N"
    CF: =$F4="Y"

    Those all go in cell F4?

    And, will this change the cell background color at all based on Y or N?

    I'm attaching the new version of the .xlsx
    Attached Files Attached Files

  4. #4
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: IF THEN or Conditional Formatting question

    Hi Rivanthe5th
    Attached file it works for me. If you click on cell F4 and go to conditional formatting on the home tab, manage rules, click on one of the rules and click edit rule, you should see the formula there
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: IF THEN or Conditional Formatting question

    ahh ok cool thanks! Works like a charm!

    I think I understand that formula for the most part. If cell "W" is < today, cell "X" is < today, or cell "Y" is < today, then cell "Z" is N.

    Just to help me understand, the =$F4="N" put in conditional formatting, whats that mean? I guess the $ is really all that I dont understand, I take it "=" informs excel that it's an equation, if cell F4 equals "N", then apply chosen format... but what's the $ do?

    Thanks again!

  6. #6
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: IF THEN or Conditional Formatting question

    The $ in this instance locks the condition to column F. So if you drag the conditional formatting to the right it will always look at F rather than progress through the columns as you copy right. If you're only copying down the rows rather than across the columns you don't need the $ in this case.

    Hope that helps make things clearer.
    If I've been of help, please hit the star

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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