Hi - in need of some help with this one.
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 availand keep getting a date of 1/2/1900. Hoping someone here can help with this.
Thanks in advance.
Hello & Welcome to the Forum,
Can you please attach a sample workbook with a clear before and after?
My first impression is to search for the min within each worksheet and then you can just search that one cell within each sheet.
But in the end, a sample workbook will help get you the most accurate solution...
---------------------------------------------------------------------------------------------------------------------------------
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
HTH
Regards, Jeff
Jeff,
Thanks for your reply. I've attached a sample of the data.
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
Please check it. I can't see any attachment.
Originally Posted by dsnow01
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Try this Array Formula in B2
Then copy down & across.
Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Haseeb,
Thanks so much for this. I am entering as an array with ctrl+shift+enter, but results are inconsistent (see attached). Not sure what I am doing wrong here...Please advise.
So, I modified the formula based on what I thought the references would be in the master workbook. Results were #N/A, but can't make out where I am going wrong. The array formula I used is this:
=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),))))
Last edited by dsnow01; 09-14-2013 at 12:29 AM. Reason: included modified formula
I am not sure what is wrong on that file. Could you please manually enter your output?
Blue highlighted must be 1Please Login or Register to view this content.
Assuming dates are starting in Q column, so change Red highlighted to COLUMN(Q$1)
@Haseeb A,
Your assumption is correct. I made the appropriate changes. Results=#N/A
Would you kindly PM me?
Last edited by jeffreybrown; 09-14-2013 at 01:50 PM. Reason: As per Forum Rule #12, please don't quote whole post unless necessary -- it's just clutter.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks