+ Reply to Thread
Results 1 to 4 of 4

If Statement Question

  1. #1
    Forum Contributor
    Join Date
    05-15-2015
    Location
    Orlando, FL
    MS-Off Ver
    Office Standard 2016
    Posts
    250

    If Statement Question

    In my spreadsheet, i have columns W through Z showing dates of insurance. What i would like to do is hide these columns and have column V be more of a "summary" of these insurances. For example, if any of them are expired (being less than today), it would simply list the name of insurance that is expired. My issue is you could have multiple insurances that are expired, not necessarily in order. Is there any better way to do this then creating a formula that has if statements referencing every possible scenario?

    Ins Example.PNG

    Above is a screen shot. For for example, on the bottom highlight, you seel GL, WCB and Excess are all expired. So i would want it to say "GL,WCB, EXCESS EXPIRED". If they were all expired, it would simply say "ALL INS EXPIRED". If there is no way to do this than a ton of if statements, then just let me know.

    Thank you.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: If Statement Question

    COUNTIF would determine the total number of insurances expired, If it equals 4 then "ALL INS EXPIRED"

    Use Condtional Formatting to highlight relevant insurances that have expired.
    Without seeing cell and column references I can't provide a full solution.

    Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does n
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,595

    Re: If Statement Question

    One way:

    =TRIM(MID(IF(B2 < TODAY(),","&$B$1,"")&IF(C2 < TODAY(),", "&$C$1,"")&IF(D2 < TODAY(),", "&$D$1,"")&IF(E2 < TODAY(),", "&$E$1,""),2,255)&IF(COUNTIF(B2:E2," < "&TODAY()) > 0,", Expired",""))
    Attached Files Attached Files
    Glenn



  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: If Statement Question

    Put the following table somewhere and call it "TextLookup".

    Assuming the status is in column-A put the following in A2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See the attached workbook.

    I think probably the only virtue is that there is not an IF statement in sight
    Attached Images Attached Images
    Attached Files Attached Files

+ 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. [SOLVED] If statement question
    By iceman2014 in forum Excel General
    Replies: 3
    Last Post: 07-28-2014, 07:41 AM
  2. IF Statement question...
    By AndySSI in forum Excel General
    Replies: 7
    Last Post: 05-31-2012, 10:23 AM
  3. Have question about using .find statement and brackets around After:= statement
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-09-2011, 03:28 PM
  4. question with if statement
    By behrk2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-23-2007, 10:45 AM
  5. if statement question
    By SMAlvarez in forum Excel General
    Replies: 1
    Last Post: 04-07-2006, 10:40 PM
  6. [SOLVED] IF Statement Question
    By Scott in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. IF Statement Question
    By Scott in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 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