Hi all,
I have a sample spreadsheet attached where I want to combine a vlookup with possibly conditional formatting.
What I need to do is look up a product produced form a data validation cell this will give me the location, but I also need to know if the product is in date or expired.
Not sure what formulas I shopuld be using.
Can someone possibly mock up a working sample with the correct formulas to show me please?
Thanks in advance,
Tony
Maybe like:
Into I6 put:
Formula:
Please Login or Register to view this content.
accept with CSE and drag it down few rows down.
so whatever you pick in cell F6 in col. I you will get location ID with value from F6 and date > today +30.
If you add into K6:
Formula:Please Login or Register to view this content.
and drag it down few rows you also get related date.
check attached file.
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
Hi,
Thanks for taking the time to reply to this, it is really appreciated. Nearly there with this.
For some reason the cells don`t populate if I select products in cells A3, A4 and A5. I need to highlight the fact that they are past the sell by date of 30 days for whatever is in col C. Hope this makes sense?
Best regards,
Tony
Try this array to populate the cell K6:
=INDEX($C$3:$C$9,MATCH(1,($F$6=$A$3:$A$9)*($I$6=$B$3:$B$9),0))
Press Ctrl+Shift+Enter to make it an array formula.
I also included the highlight cell rule to make it red if past 30 days.
The range of dates are also highlighted if expired in this new Revision.
Last edited by billgyrotech1; 02-07-2019 at 10:39 AM.
IN helper column R. In R1 then copy down
=IFERROR(INDEX($B$3:$B$9,AGGREGATE(15,6,ROW($B$3:$B$9)/($A$3:$A$9=$F$6),ROWS($R$1:$R1))-ROW($A$3)+1),"")
formula for validation of I6
=OFFSET($R$1,0,0,COUNTIF($R$1:$R$10,"*?"))
Formula for K6
=VLOOKUP(I6,$B$3:$C$9,2,0)
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Hi kvsrinivasamurthy,
Thanks for trying to sort this, I have still got 2 issues:
1. When I enter your formula in cell I6 I receive the message " This value doesn`t match the data validation restrictions defined for this cell.
2. How can I get the results for Product code 6666 if there is more than one location as per cells A8 and A9?
Thanks in advance,
Tony
Have you tried my revision?
For me working ok.Pl upload sample file showing the problem. List is made available in Column R. Same list is used for validation of cell I6.
Formula is for validation in I6.Do not enter in I6.
Hi Billgyrotech1,
Yes I have tried your plan but cant seem to get it functioning correctly because:
If I enter product 1,2 or 3 in cell F6 then it is not populating the location in cell I6
the cell K6 just returns #N/A. I am also struggling to return the product locations of 6666 (A8 & A9) as I only get the one location returned.
Hope this makes sense to anyone out there?
Best regards and thanks guys,
Tony
Hi kvsrinivasamurthy,
Please see attached as requested.
Strangely though this appears to be working now.
Still need to return the locations of multiple dates/locations if the products are in more than 1 place as per product 6666.
Thanks again for all the help.
Tony
The reason you are getting the #N/A is because there isn't a matching product and location.
I you want to list all of the products by date that is a different question which can also be done.
In my opinion it's not working as you need because if I choose 6666 I've got only 1 location (A6).Originally Posted by Tony0731
I wrote in my previous post. If you choose 1111 there are not location with date > today + 30 as intended.
(I've seen as you changed your request in initial post).
Hi Kokosek,
There should be 2 locations returned for product 6666, A6 & A7 however the way this is setup it only returns the first location.
Any ideas?
Thanks again,
Tony
In my org attached file, if you pick 6666 in F6 you will get two location in I6 and I7.
Did you check?
Capture.JPG
Hi again Kokosek,
Yes, you are correct more than one location is returned as per your solution, however nothing populates in i6 or K6 if I enter products 1111,2222, or 3333.
Cheers buddy and thanks for your help.
Best regards,
Tony
Because date related to 1111,2222,3333 are not later than today + 30. That was your original request.
Double sent for some reason.
Last edited by KOKOSEK; 02-07-2019 at 12:05 PM. Reason: double sent for some reason
Tony,
I didn't realize you wanted all returns for 'Location' and 'Use By' when entering the 'Product' number. This latest file will do that for you.
I hope this helps,
Bill
Hi Billygyrotech1,
Thank you so much for this it is exactly what I need.
I must admit though that the formulas you have used have gone straight over my head, far to advanced for me.
I guess I have a lot to learn!!
Thanks also to KOKOSEK and any other replies received for your help with this too.
Best regards,
Tony
Tony,
I am glad to help. If this solves your original question please mark this as solved.
Done!! Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks