Truly dynamic list generated by selection of static list?

[obidon]

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.

[JohnTopley]

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

[obidon]

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!

[JohnTopley]

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

[obidon]

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!

[JohnTopley]

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

[obidon]

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.