+ Reply to Thread
Results 1 to 3 of 3

Drop down list using combo box and dynamic named ranges

  1. #1
    Registered User
    Join Date
    07-20-2011
    Location
    London
    MS-Off Ver
    365
    Posts
    17

    Drop down list using combo box and dynamic named ranges

    Hello

    I have been learning all about Dynamic Named Ranges recently and have bee attempting to integrate this into a drop down list for a holiday planner (which uses a combo box) I use for work.

    I can get the drop down list / combo box working fine and I can get the Dynamic Named range to work (expanding and updating with new entries) However I cannot get them to work together.

    In the attached example I have
    Holiday Entry - A2 I added under the data validation / List =OFFSET(Sheet2!$B$3,0,0,COUNTA(B:B)-1)
    However when I double click the combo box it doesn’t contain any entries.

    In the cell below (A3) the drop down list / combo box works fine but the data validation is just aimed at a named range.

    What I want to be able to do is have the combo box (enabling partial typing / name suggestion when you type in it) and Dynamic Named range (enabling the main list to be updated / modified and automatically update any drop downs) working together. I’m pretty sure it is possible, I just don’t know quite how

    Hope that makes sense and someone might have a solution?

    Thanks
    Paul
    Attached Files Attached Files

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Drop down list using combo box and dynamic named ranges

    Hi,

    Change the defined name People to be
    =OFFSET(Sheet2!$B$3,0,0,COUNTA(Sheet2!$B:$B)-1)
    then in the data validation for column A just use
    =People
    and then your code will work.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    07-20-2011
    Location
    London
    MS-Off Ver
    365
    Posts
    17

    Re: Drop down list using combo box and dynamic named ranges

    Oh so simple (when you know how!)

    Thank you very much

+ 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. Dependent List Validation from Dynamic Named Ranges
    By freeride in forum Excel General
    Replies: 11
    Last Post: 01-22-2020, 03:07 PM
  2. [SOLVED] Populate userform combo box with named dynamic list
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-28-2014, 05:17 PM
  3. Dynamic Named Ranges & Drop Down Lists
    By student6 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-02-2014, 01:07 AM
  4. [SOLVED] How Can I Build A Dynamic List From Multiple Named Ranges
    By GEANZ in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-02-2013, 01:56 AM
  5. [SOLVED] Adding dynamic named ranges to combo boxes in userform
    By Spritz in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-04-2013, 09:45 AM
  6. [SOLVED] Dynamic Ranges as drop down lists based on another drop down list
    By Excel_Beginner_1 in forum Excel General
    Replies: 4
    Last Post: 05-15-2012, 03:31 PM
  7. Drop-down-list with Named ranges
    By Chootje in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2006, 07:55 AM

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