Find MIN date across multiple worksheets based on multiple instances of a Unique-ID

  • I need to search for a unique ID across multiple workbooks and find the oldest date associated with each ID. I've used several different versions of MIN formulas and also tried a vlookup/if formula to no avail and keep getting a date of 1/0/1900.


    I would use the application tab to search server, storage, people & database tabs for each Unique reference.
    Once located (and there may be several instances), the oldest date for that Unique reference should populate in the respective MS column.


    *Do note: The people tab has an extended Unique reference that would only match the numerical portion of the Unique reference in the server, storage and database tabs.


    For example if looking up unique reference 26575-1 it would be located in the Server tab and populate dates in the Application tab as follows:


    Unique Reference "MS 1 "MS 2 "MS 3 "MS 4 "MS 5 "MS 6
    26575-1 7/1/2013 11/29/2013 1/4/2014 2/22/2012 3/19/2013 4/27/2013


    I modified the formula based on using columns r-u in the master workbook. Results were #N/A, but can't make out where I am going wrong. The array formula I used is:


    =MIN(IF(T(OFFSET(INDIRECT({"STORAGE","SERVER","PEOPLE","DATABASE"}&"!p1"),ROW($P$2:$P$200)-ROW($P$2),))=$P2,N(OFFSET(INDIRECT({"STORAGE","SERVER","PEOPLE","DATABASE"}&"!"&ADDRESS(18,COLUMNS($R2:R$2)+1)),ROW($P$2:$P$100)-ROW($P$2),))))


    Many thanks for any help with this!!


    http://www.excelforum.com/exce…tml?p=3405767#post3405767

  • Re: Find MIN date across multiple worksheets based on multiple instances of a Unique-


    The date you're getting is excel reading 0 as date format I think... As for the rest, can you upload the workbook or a dummy form of it? Thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!