+ Reply to Thread
Results 1 to 23 of 23

Data validation list with lookup

  1. #1
    Registered User
    Join Date
    12-09-2014
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    19

    Data validation list with lookup

    Hello there,

    I hope someone could point me in the right direction:

    I have my table something like this:


    Contract Subcontract ID SPID
    21673000 21673000-7 49 1,231
    11873001 11873001-18 48 1,232
    11873001 11873001-17 47 1,232
    11873001 11873001-16 46 1,232
    11873001 11873001-15 45 1,232
    11873001 11873001-14 44 1,232
    11873001 11873001-13 43 1,232
    11873001 11873001-12 42 1,232
    11873001 11873001-11 41 1,232
    21673000 21673000-6 40 1,231
    21673000 21673000-5 39 1,231
    21673000 21673000-4 38 1,231
    21673000 21673000-3 37 1,231
    11873001 11873001-10 36 1,232
    11873001 11873001-9 35 1,232
    11873001 11873001-8 34 1,232
    11873001 11873001-7 33 1,232
    11873001 11873001-6 32 1,232
    11873001 11873001-5 31 1,232
    11873001 11873001-4 30 1,232
    11873001 11873001-3 29 1,232
    11873001 11873001-2 28 1,232
    21673000 21673000-2 27 1,231
    11873001 11873001-1 26 1,232
    21673000 21673000-1 25 1,231


    and in other sheet I would like to have a field where the user type the contract number and below a data validation shows a drop down with all of the "Subcontracts" that matches the contract.

    Is this possible?

    Or maybe have the Contract field to autocomplete to speed up the typing process also?



    Contract 11873001

    Subcontract 11873001-18 Drop down values from 01-01 to 01-18



    Thanks in advance for any response

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data validation list with lookup

    Please confiorm the Excel version you are using, and update your profile, if needed.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    12-09-2014
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    19

    Re: Data validation list with lookup

    Excel 2019

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Data validation list with lookup

    Thanks - please update your profile.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data validation list with lookup

    I set up a Named Range (contracts) to auto-adjust to fit the number of contracts:

    =Sheet1!$A$2:INDEX(Sheet1!$A:$A,AGGREGATE(14,6,MATCH({1E+100,"Zzzz"},Sheet1!$A:$A),1))

    and then used this formula to return a unique contract list ("Contracts" in K2):

    =IFERROR(INDEX(Contracts,MATCH(1,INDEX(--ISNA(MATCH(Contracts,K$1:K1,)),),)),"")

    and another Named range, to capture the unique contracts (and no blanks "Uniques")

    =Sheet1!$K$2:INDEX(Sheet1!$K$2:$K$14,SUMPRODUCT(--(LEN(Sheet1!$K$2:$K$14)>0)))

    These can be seen in the Name Manager (CTRL-F3).

    Then in sheet 2, I created a dropdown with data Validation list: =Uniques

    then.. in A5, copied across and down:

    =IFERROR(INDEX(Sheet1!B:B,AGGREGATE(15,6,ROW(Contracts)/(Contracts=Sheet2!$B$1),ROWS(A$5:A5))),"")

    Currently, it is set for 13 contracts. MAXIMUM how many will you have? I can then tweak the named ranges to suit.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-09-2014
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    19

    Re: Data validation list with lookup

    Hey thank you very much for your prompt response. see the file
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data validation list with lookup

    See my file, instead, please...

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Data validation list with lookup

    On the Data sheet in H2 (this can be anywhere, even in a hidden column):

    =FILTER(Table1[Subcontract],Table1[Contract]=Sheet2!E4)

    Data Validation for list:

    =Data!$H$2#

    EDIT: Sorry - won't work. You have 2019, not 365. Damn! However. I've attached the sample workbook for anyone looking for something similar who can take advantage of the dynamic array functions in MS365.
    Attached Files Attached Files
    Last edited by AliGW; 02-19-2021 at 12:10 PM.

  9. #9
    Registered User
    Join Date
    12-09-2014
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    19

    Re: Data validation list with lookup

    Hi thanks again. Sorry to be a pain.
    :-(

    I am checking your file but I don't think is what I am looking for. or please apologize if I explain it incorrectly. English is not my first language.

    In the excel file that I just sent you. I have added two marks for the expected output.

    Basically is this:

    In one cell I would like to type "Manually" the contract number.
    In other cell I would like to have a dropdrown that filters the table based in the contract number cell typed and shows all of the possible matches.

    Makes sense?

    Sorry again

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data validation list with lookup

    Before I spend more time on this... can I sort the list alphabetically? If so, it can be done seamlessly.

  11. #11
    Registered User
    Join Date
    12-09-2014
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    19

    Re: Data validation list with lookup

    Hi, sure. We can sort the list. BTW the list is a table connected with a Share Point lits So I can either sort directly form SP or in the column Subcontractor. Sorry I have been reading so many post and no luck

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data validation list with lookup

    With the data sorted, first by contract, then by subcontract, set up this Named range (Subcontract):

    =INDEX(Data!$B:$B,AGGREGATE(15,6,ROW(Table1[Contract])/(Table1[Contract]=Sheet2!$E$4),1)):INDEX(Data!$B:$B,AGGREGATE(14,6,ROW(Table1[Contract])/(Table1[Contract]=Sheet2!$E$4),1))

    Then set the DV on the dropdown as =Subcontract

    Why not also have the contract in a drop-down? It makes for error-free entry?
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-09-2014
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    19

    Re: Data validation list with lookup

    OMG!!! Thank you very much that is exactly what I was looking for. I was thinking also a DD for the Contract column but eventually that column will be a huge list and will be a pain for the user to scroll down and select the contract.
    But a BIG THANK YOU!!!

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data validation list with lookup

    Can the contracts be broken down in some way... by the first few characters, or something??? Just to make it possible to do..

    Think about it.

    For now...


    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data validation list with lookup

    When I said broken down, I actually meant grouped...

  16. #16
    Registered User
    Join Date
    12-09-2014
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    19

    Re: Data validation list with lookup

    Hi there, would you mind to help me out?
    I did exactly what you did, changed the fields to match my current situation, noticed that in my table I have first the column Subcontrat "A" and then the column Contract "B", so I have added a new column at the end of my table to be equal to subcontract. But I can't get this to works.
    Any help please?

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data validation list with lookup

    My crystal ball is away for repair at the moment...

    Post a sample sheet.

  18. #18
    Registered User
    Join Date
    12-09-2014
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    19

    Re: Data validation list with lookup

    Thank you very much for your prompt response, here is the file
    Attached Files Attached Files

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data validation list with lookup

    You're lucky to have caught me... about to pour a beer....

  20. #20
    Registered User
    Join Date
    12-09-2014
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    19

    Re: Data validation list with lookup

    I definitely owe you a beer, sorry to be a pain.

    Much appreciated!!

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data validation list with lookup

    OK. Fixed. Going for that beer now... a nice home made USA-style IPA.
    Attached Files Attached Files

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data validation list with lookup

    No need for that extra column, but table DOES need to be sorted alphabetically, as previously described.

  23. #23
    Registered User
    Join Date
    12-09-2014
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    19

    Re: Data validation list with lookup

    Yes, OMG. Finally

    THANK YOU !!!
    Last edited by sajarac; 02-20-2021 at 03:00 PM.

+ 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. INDIRECT for Data Validation List, with embedded LOOKUP
    By ChrisMattock in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-16-2020, 10:03 PM
  2. [SOLVED] Lookup based on data validation list
    By sjgidman82 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-10-2019, 01:05 PM
  3. Data Validation List Associated with a Cell Value Lookup
    By kishoremcp in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-29-2018, 02:15 PM
  4. Lookup in a Data Validation List
    By Simon.xlsx in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-28-2014, 09:23 AM
  5. [SOLVED] Multiple Result Lookup inside Data Validation List
    By Whizbang in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-13-2014, 12:53 PM
  6. Create data validation based on lookup to get a list
    By Rocky2013 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-16-2013, 11:31 AM
  7. VBA for variable lookup from data validation list
    By Ravenous in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-15-2013, 01:03 AM

Tags for this Thread

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