+ Reply to Thread
Results 1 to 9 of 9

Searchable dropdown list using data validation, named ranges and Offset formula

  1. #1
    Registered User
    Join Date
    01-07-2020
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft 365
    Posts
    9

    Exclamation Searchable dropdown list using data validation, named ranges and Offset formula

    Hi Forum members,

    I have found many, many different approaches on this subject but I have been unable to tweak them to fit my scenario. Below is my scenario which references the attached spreadsheet.

    The data in Column A of the Congregations tab forms the named range Cong_Name. This named range becomes the source for data validation lists in Column F of the Bookings tab. However, the data in the Cong_Name range is 200+ lines and growing so a standard data validation is not user friendly. Thus, I am searching for a solution that dynamically reduces the list values based on the characters entered into a cell of Column F of the Bookings tab. Additionally, at least 200 cells in Column F of the Bookings tab require the data validation list, not just one field.

    The best solution that I have found that has closely resembled my needs is from Neil Firth in two parts on Youtube. (See attached screenshots for URL links to these videos)
    • Create a searchable drop down list in Excel
    • Create a searchable drop down list in Excel Part 2

    I have come unstuck with the =CELL("contents") formula in the last part of the 2nd video and cannot replicate the same end result. Not sure if it an error with how I have replicated the formulas or based the formulas span 2 tabs instead of the 1 tab as in his video. I have reached out to Neil but yet to hear back from him so I was hoping that there might be some Excel experts out there that can point me in the right direction.


    Many thanks,
    Tim
    Last edited by timjo-b; 01-07-2020 at 11:39 PM.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Searchable dropdown list using data validation, named ranges and Offset formula

    here it is
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: Searchable dropdown list using data validation, named ranges and Offset formula

    If you're ok to use vba & combobox instead of data validation, here's an example:
    It's a searchable combobox that can appear and hide automatically. So you need only 1 combobox that will pop up when you select a cell in a certain range.


    In the top of the sheet's code module, you need to adjust the code:
    == YOU MAY NEED TO ADJUST THE CODE IN THIS PART: ====

  4. #4
    Registered User
    Join Date
    01-07-2020
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft 365
    Posts
    9

    Re: Searchable dropdown list using data validation, named ranges and Offset formula

    Hi Akuini and tim201110,

    Thank you for your quick responses and the sample workbooks, it is very helpful to see the workings of your different methods.

    I am personally happy to use combo boxes and VBA but mindful of the fact that this workbook will be inherited by people who might be beginner level Excel users. Thus, the aim is to keep formulas to the backend so future users only have to be involved with data entry.

    I am unsure from where I downloaded the attached workbook, but almost delivers my desired end result with exception of one small but crucial problem. Cell M1 contains the formula (see attached workbook for formula) which references Column B where the data validation is located. My problem is that my data validations lists will be located in a different sheet i.e., not in Sheet1 of the attached workbook and thus the CELL function does not work. Is there a workaround to point the above formula to a column in a different sheet or is this not possible with this method?

    Many thanks,
    Tim
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: Searchable dropdown list using data validation, named ranges and Offset formula

    Sorry, my knowledge of excel formula is very basic.
    Hopefully someone here can help you.

  6. #6
    Registered User
    Join Date
    01-07-2020
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft 365
    Posts
    9

    Re: Searchable dropdown list using data validation, named ranges and Offset formula

    Hi tim201110,

    I have replicated the VBA code, formula and textbox in your spreadsheet into my spreadsheet (attached). However, after making all the necessary adjustments to the VBA code, when I try to enter text into Cell F2 (Congregations sheet), the following error appears.

    Run-time error '1004':
    Unable to get the ListBoxes property of the Worksheet class
    When I click Debug, it points to the line of code in the Bookings module (see attached screenshot). What have I done wrong with the code to produce this error?


    Many thanks,
    Tim
    Attached Images Attached Images
    Attached Files Attached Files

  7. #7
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Searchable dropdown list using data validation, named ranges and Offset formula

    it is better to import your data in my wb

  8. #8
    Registered User
    Join Date
    12-16-2019
    Location
    Alaska
    MS-Off Ver
    office 2010
    Posts
    13

    Re: Searchable dropdown list using data validation, named ranges and Offset formula

    Hi,
    I've created a dynamic searchable data validation list on an invoice template to bring data from the products sheet to the home sheet. Value can be searched the data validation lists we create with a formula and a small VBA code.

    searchable-dataval-list-nw.gif

    Data Validation - Settings - Source 's formula :
    Please Login or Register  to view this content.
    I added the sample template, you can review it .
    Attached Files Attached Files
    Last edited by kadr; 01-21-2021 at 04:53 AM.

  9. #9
    Registered User
    Join Date
    02-22-2021
    Location
    Christchurch, New Zealand
    MS-Off Ver
    365
    Posts
    1

    Re: Searchable dropdown list using data validation, named ranges and Offset formula

    Hello Akuini,
    Thank you very much for this code, really great!
    Is there a way to make the search ignore keyword order, like your other VBA example?

    "The search ignores the keywords order, so keywords "ma la" will find "Maryland" and "Alabama""

    When I use your other method, I often get an error '70' with the .List = ary or .List = vList.

    Thank you again,
    Mike

+ 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. [SOLVED] VBA Possibly: Searchable list while using data validation through a named range in a table
    By AMoreno in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 10-03-2019, 10:47 AM
  2. Replies: 3
    Last Post: 01-17-2018, 11:55 AM
  3. [SOLVED] Data Validation & Dynamic named ranges - full list not showing
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-19-2017, 06:55 AM
  4. COUNTIF referencing different named ranges in dropdown list
    By dutton in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2013, 10:01 AM
  5. Replies: 1
    Last Post: 02-27-2013, 01:57 PM
  6. Replies: 4
    Last Post: 06-11-2012, 06:17 PM
  7. printing named ranges from a validation list
    By pixifaery in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-19-2010, 12:12 PM

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