+ Reply to Thread
Results 1 to 7 of 7

Truly dynamic list generated by selection of static list?

  1. #1
    Registered User
    Join Date
    03-28-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    9

    Truly dynamic list generated by selection of static list?

    Hi, I've been long time member but never needed to post because I could always find my solution with searches. I've performed searches for my current issue but my problem is slightly different than the results I am receiving.

    I am looking to create a dynamic list (combo box) based on a single entry in one column and the corresponding items in an adjacent column.

    Example attached; On the Receipts sheet, I would like to check A (Type) to find the value of F16 and create a list (combo) of all unique B (Sub Type) items in G16 related to F16. It seems as simple as a Lookup, but you can't put a lookup in Data Validation (at least I can't get it to work).

    This is all so I can perform a calculation in H16 based on the selection in G16; I should be good with the calculation, I just don't know how to make the Data Validation List be dynamic based on the values that exist in B and relation to A. I also may be making the whole idea more difficult than needs be. :p

    I've currently set my list limit to 254 (i.e. A2:A255).

    example.xlsx

    Example: by selecting Gas in F16, G16 will populate with all Sub Types related to Gas (i.e. Petro Canada, Pioneer) which would in turn produce the total of all Amounts (D) in H16 based on the G16 selection. Gas > Pioneer > 189.92

    Any help or ideas is greatly appreciated.
    Last edited by obidon; 08-04-2015 at 01:37 PM. Reason: included wording of example output based on attached file

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,273

    Re: Truly dynamic list generated by selection of static list?

    This a helper column (in K) which extracts the sub-groups for a Type in F!6.

    =IFERROR(INDEX($B$2:$B$32,SMALL(IF($A$2:$A$32=$F$16,ROW($A$2:$A$32)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

    Enter with Ctrl+Shift+Enter

    As it contains duplicates then formula in L2 downwards gets a unique list :

    =IFERROR(INDEX($K$2:$K$15, MATCH(0,COUNTIF($L$1:L1, $K$2:$K$15), 0)),"")

    Enter with Ctrl+Shift+Enter

    This is a named range (Sub_group) which is used in Data Validation in G16; you need to select to change listing.

    H16 contains summation of values.

    See attached
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-28-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Truly dynamic list generated by selection of static list?

    Thank you for the response.

    While this solution works it has limits which does not fully meet my needs. I am also a stickler for presentation and this does not present well.

    The solution appears to be related to dynamic lists in that the lists need to be created before they can be used in data validation.

    You have helped to steer me in a much better direction than I was headed. Thanks again!

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,273

    Re: Truly dynamic list generated by selection of static list?

    The list (there is only one) is created dynamically after the selection in F16. Nothing else is required.

  5. #5
    Registered User
    Join Date
    03-28-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Truly dynamic list generated by selection of static list?

    As I said, it works and I thank you for the effort you put in.

    My problem is that the list is a static size which can be seen when hitting the second drop down, 12 positions, many blank, plus a trailing zero; I am looking for the list to grow as the sub-type grows, only show what exists with nothing extra displayed.

    As I said, you have given me a ton of information to continue to attempt to accomplish as my requirements may be reduced.


    Thanks again!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,273

    Re: Truly dynamic list generated by selection of static list?

    The list is apparently a static size but will grow as long as required so you could add types/sub-groups without having to concern yourself with creating more lists.


    The formulae need to be dragged down to allow for growth. You can always hide the columns containing the lists or move them "out of the way" somewhere". I only coloured the column as a guide to show the formula extended to many rows.

    The trailing zero is a bit of a mystery as I initial had it, but it "disappeared" at some stage during my testing: see the attached - columns hidden.

    The other option is to create dependent drop-down lists but this usually means adding to them manually although the "length" of them can be controlled dynamically.

    See


    http://www.contextures.com/xlDataVal02.html
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-28-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Truly dynamic list generated by selection of static list?

    Thank you for all the help, the dynamic list can be created from the lists you gave me through a new named list using OFFSET and counting the height; example attached.

    example.xlsx

    Thanks again for all your help, this question is resolved.

+ 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. Creating a dynamic dropdown list of a selection of a master list
    By RooMar in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 05-15-2015, 05:27 AM
  2. Data Validation list with static and dynamic values
    By jontherev in forum Excel General
    Replies: 2
    Last Post: 04-25-2014, 01:35 PM
  3. generated parts list from list of assemblies
    By staticchanger in forum Excel General
    Replies: 1
    Last Post: 06-24-2012, 05:20 PM
  4. Dynamic dropdown list selection based on combo box
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-10-2012, 10:55 AM
  5. Populating a Table from a Dynamic List selection
    By seanfoxen in forum Excel General
    Replies: 3
    Last Post: 12-19-2011, 01:38 PM
  6. Replies: 2
    Last Post: 09-24-2010, 12:11 PM
  7. Dynamic drop down list selection (2007)
    By jik_ff in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-22-2010, 08:23 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