+ Reply to Thread
Results 1 to 11 of 11

Multi-column dropdown list using multi-column dynamic range (no VBA)

  1. #1
    Registered User
    Join Date
    08-05-2017
    Location
    UTC+07:00
    MS-Off Ver
    2016 64-bit on Win10 Pro 1703
    Posts
    54

    Multi-column dropdown list using multi-column dynamic range (no VBA)

    I have one ActiveX combobox (combobox1) and a table with 3 columns of data (Table1 [Col1],[Col2],[Col2]).

    What I'm trying to do is display the whole table, or at least two of the columns (without heading) in the dropdown list.

    The best I could do is type the existing data range, A2:C30 (first row being the heading), into the ListFillRange property. That worked, but the range was static, not dynamic.

    What I have tried so far:
    1. Defined a named range, using OFFSET(A2,0,0,COUNTA($A:$A)-1,3)), which included all three columns -- Rejected.
    2. Typed the table name [Table1] in the ListFillRange property -- Rejected.
    3. Defined three dynamic ranges, each representing a column in Table1, and entered Column1:Column3 in the ListFillRange property -- Rejected.
    4. Typed Table1[#Data] into ListFillRange -- Rejected.

    In short, nothing worked unless I specifically circled out the cell range. Single-column dynamic range sources worked, but not multi-column.
    So what other options do I have? No VBA allowed here, or else I would have done it already.

    There's a second part to this question, but I'll leave that for later.

  2. #2
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,375

    Re: Multi-column dropdown list using multi-column dynamic range (no VBA)

    When you use a table as range (without the header) but all the datalines included then the fillrange will be adjusted iif you add or delete rows in the table. So it will act as an dynamic range.
    In other words if the fillrange is the same cells that the named range =Table1 gives then your dropdownlist will adjust dynamicly

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,019

    Re: Multi-column dropdown list using multi-column dynamic range (no VBA)

    See here:

    https://www.youtube.com/watch?v=JwA2gAbEXic

    Yes it is VBA so there maybe no other option.
    Last edited by JohnTopley; 06-01-2018 at 11:05 AM.

  4. #4
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,375

    Re: Multi-column dropdown list using multi-column dynamic range (no VBA)

    I tested my previous answer in excel 2016 version and there it works as dynamic. Not sure about older versions.

    As an alternative if you cannot get it to work I used following method once.
    - Make a colum 4 and merge the results of column 1 2 and 3 to show in the combobox with dynamic range method you know to work.
    - link the combobox to a cell use the value in the cell as lookup value to search the correct line and column(s) of output wanted

  5. #5
    Registered User
    Join Date
    08-05-2017
    Location
    UTC+07:00
    MS-Off Ver
    2016 64-bit on Win10 Pro 1703
    Posts
    54

    Re: Multi-column dropdown list using multi-column dynamic range (no VBA)

    Quote Originally Posted by Roel Jongman View Post
    When you use a table as range (without the header) but all the datalines included then the fillrange will be adjusted iif you add or delete rows in the table. So it will act as an dynamic range.
    In other words if the fillrange is the same cells that the named range =Table1 gives then your dropdownlist will adjust dynamicly
    Well I just gave the whole table in as FillRange as well as three separate dynamic ranges, each representing a column, like I said in the first post. It just won't accept more than one column of dynamic range.
    Quote Originally Posted by John Topley
    See here:

    https://www.youtube.com/watch?v=JwA2gAbEXic

    Yes it is VBA so there maybe no other option.
    Yes, I know that works. But I can't have VBA here. Just trying to see how far I can go without VBA

    Quote Originally Posted by Roel Jongman
    I tested my previous answer in excel 2016 version and there it works as dynamic. Not sure about older versions.
    As an alternative if you cannot get it to work I used following method once.
    - Make a colum 4 and merge the results of column 1 2 and 3 to show in the combobox with dynamic range method you know to work.
    - link the combobox to a cell use the value in the cell as lookup value to search the correct line and column(s) of output wanted
    Well I'm on Excel 2016 as well. Works as dynamic, single-column, but not more.
    I could make a column 4 and merge all 3 previous columns, but that wouldn't make column 4 dynamic, would it?

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Multi-column dropdown list using multi-column dynamic range (no VBA)

    I could make a column 4 and merge all 3 previous columns, but that wouldn't make column 4 dynamic, would it?
    It would. As table will auto carry any formula (as long as it's consistent relative reference) as new data are added.
    Last edited by CK76; 06-01-2018 at 12:28 PM.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  7. #7
    Registered User
    Join Date
    08-05-2017
    Location
    UTC+07:00
    MS-Off Ver
    2016 64-bit on Win10 Pro 1703
    Posts
    54

    Re: Multi-column dropdown list using multi-column dynamic range (no VBA)

    Quote Originally Posted by CK76 View Post
    It would. As table will auto carry any formula (as long as it's consistent relative reference) as new data are added.
    Good to know that. I don't know how exactly to do that merge or lookup yet but I'll try before coming back for more instructions. I'll post my results. Thanks.

  8. #8
    Registered User
    Join Date
    08-05-2017
    Location
    UTC+07:00
    MS-Off Ver
    2016 64-bit on Win10 Pro 1703
    Posts
    54

    Re: Multi-column dropdown list using multi-column dynamic range (no VBA)

    Ok...the way I figured it, the merge method is essentially joining the values of the previous 3 columns into one long string, and format it a little bit to properly show up in the combobox.

    If I'm right, then first of all, it is still a single-column list, and I'll have to switch to the Form Control version of the combobox so that it will return the row number in the linked cell, which I will then use to lookup values in other columns.

    What I actually wanted to do is: show data from column 2 in the dropdown but bound the linked cell to column 3. That way I don't need to assign additional helper cells.

  9. #9
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,375

    Re: Multi-column dropdown list using multi-column dynamic range (no VBA)

    yasdai7287,
    I'm sorry but all your posts there are a lot of wrong assumptions, it is not doable to explain all your wrong assumptions as that will not bring you closer to where you need be.
    Just for a moment, forget all you know about what fixed ranges and dynamic ranges should look like..
    I'm gonna give it one more try with an example that will be based on my first answer that filling A2:A30 as tablerange without headers will act dynamic. It is specific difference between named ranges and combobox that a normal cellrange will act as dynamic range in a combobox if that cellrange is part of a excel table.
    Whenever you need a combobox you also need a linked cell to show the value of the chosen value bound column.
    Only if you are using the value in a macro then you might not need to use the linked cell because you then write the selected value to a vba variable

    In the example file I have included the correct property settings for it to work

    please try expanding or reducing the table size to see it has immediate effect on shown list in the combobox.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-05-2017
    Location
    UTC+07:00
    MS-Off Ver
    2016 64-bit on Win10 Pro 1703
    Posts
    54

    Re: Multi-column dropdown list using multi-column dynamic range (no VBA)

    Roel,
    Thanks for your time and your file. At a glace, it's all raw data with some formulas. I'll set up a dropdown list and see how it works with this data and formula.
    Thanks a lot also for pointing out my bad habits.

  11. #11
    Registered User
    Join Date
    08-05-2017
    Location
    UTC+07:00
    MS-Off Ver
    2016 64-bit on Win10 Pro 1703
    Posts
    54

    Re: Multi-column dropdown list using multi-column dynamic range (no VBA)

    Well, the file has 4 sheets, with 5 columns of data in each sheet (range format). Column E is a merge of A and D, and there is one other cell that takes the sum of D1:D50 on all sheets. Column D is somewhat "dynamic." I add numbers in there and the result is reflected in the sum sell -- until I exceed D50. At least this was what I could make out from the original data in the file.

    So how is this different from my own set of data? I then tried to set up a combo box using this data, and the result was no different than those in my original post -- It will accept only one column of dynamic range. As with the merged column, it's D1 & " " & A1, a joined string, displayed in the dropdown as single-column data. So I guess I'm back to square one.

    I'm sure I must have missed something, but anyway, thanks a lot for your patience and your time. I'm sorry I still couldn't figure it out after all your efforts, and I will keep trying.

+ 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. Copy Multi Rows From Multi Sheets Based On Column Value And Create New Workbooks
    By Huskersippi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2017, 04:51 PM
  2. [SOLVED] Multi-column Listbox - Dynamic range - over 4,000 rows of data
    By s2jrchoi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-12-2013, 04:39 PM
  3. Replies: 19
    Last Post: 10-05-2012, 01:03 PM
  4. Multi-Column Listboxes correspond to multi textboxes
    By Crebsington in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2012, 01:38 PM
  5. [SOLVED] Multi-Column Dynamic Named Range...Is there an easier way?
    By Ken Johnson in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-22-2006, 09:00 PM
  6. Data Validation Multi-column Dropdown
    By Bill Sturdevant in forum Excel General
    Replies: 2
    Last Post: 02-04-2005, 02:06 PM
  7. Dynamic Multi Column ListBox
    By Ronbo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-11-2005, 06:06 PM

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