+ Reply to Thread
Results 1 to 9 of 9

Grab lowest date based on criteria

  1. #1
    Registered User
    Join Date
    01-25-2010
    Location
    Roanoke, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    53

    Grab lowest date based on criteria

    Ok, so you will you see 3 tabs for this example. The first tab shows everything I need to see, the 2nd tab is a list of all of the information, the third tab is just a list of all of the opportunities.

    You will see on Tab 1, column C, this is the formula I thought would work, clearly it is not. What is needed is that in Column C, I need the lowest date from Tab 2, that meets the same criteria. For Example.

    If on the 2nd Tab, Hollins University with the opportunity name, "2010 Six Campus Buildings", everything should show nothing, expect for under "100%" the date should read 3/5/2010.

    Any help would be great!
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Grab lowest date based on criteria

    Do you mean?

    =LOOKUP(2,1/((Sheet2!$C$2:$C$1438=A2)*(Sheet2!$B$2:$B$1438=B2)),Sheet2!$F$2:$F$1438)

    copied down.

    What do you mean by:

    everything should show nothing
    ?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    01-25-2010
    Location
    Roanoke, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Grab lowest date based on criteria

    What I meant by that, was the only field that should show up in that line is the 100% field, because that is the only one that meets the criteria.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Grab lowest date based on criteria

    So you want to add the condition that column E on Sheet2 equals 100?

    Like so:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-25-2010
    Location
    Roanoke, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Grab lowest date based on criteria

    What I want is for the first tab to summarize the information on tab 2. So, if you are looking at the first line:

    Pilot Travel center 2010 Retrofits

    Tab 2 shows that there are only things at 90% and 1 at 100%. Therefore, in the columns of 25%, it should be blank, 50%, should be blank, 75% should be blank, 90% will return the earliest date, which in this case would be 2/27/2010 and 100% should return 3/3/2010.

    Does that clarify?

  6. #6
    Registered User
    Join Date
    01-25-2010
    Location
    Roanoke, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Grab lowest date based on criteria

    The second equation worked with little change, however, it is returning the highest date, not the lowest, thoughts? I'm trying to throw a MIN function into it.... no success yet.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Grab lowest date based on criteria

    Try then in C2:

    Please Login or Register  to view this content.

    confirmed with CTRL+SHIFT+ENTER
    and copied down.

    Then copy C2 and paste to E2, G2, I2 and K2

    And copy each column down...

    To format as date and hide the 0's, format cells with custom: m/d/yyyy;;;

  8. #8
    Registered User
    Join Date
    01-25-2010
    Location
    Roanoke, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Grab lowest date based on criteria

    Here is the updated file. You will notice that I will not return the MIN date for some reason. I tried the third equation and got nothing but errors. Please let me know if you see my mistake.
    Attached Files Attached Files

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Grab lowest date based on criteria

    Did you confirm with CTRL+SHIFT+ENTER?

    See attached.

    Also note, formula in D2 should be changed to: =IF(E2=0,"",IF(C2=0,"",E2-C2)) because I have hidden 0's in the date columns.. not blanks....
    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)

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