the Holy Grail of dropdowns: 4D

[davedigerati]

Basically, I need to create a drop down list dependent on another drop down selection, but both drop down sources are in the same table that is NOT sorted and is dynamic (users continue to enter new stuff). ie "D4: Dynamic Dependent Drop Downs"

I can't post images yet, so picture a 2 column table of states and cities. Users enter new rows as needed, no sorting. Elsewhere, a drop down allows the user to select a state. The next drop down (the problem) should populate with cities matching that state selection.

I've included a link to an xls with that starting point:
https//drive.google.com/file/d/11RDfeNaQgruEbeIKuhtdEcSabzR7lqUq/view?usp=sharing

The approaches at contextures won't work on unsorted lists, and transposing all data from columns to rows like at Xelplus.com seems way too hacky and will confuse future workbook users/owners and/or won't be maintainable.

I've scratched and googled my way through every approach I can find/contrive and am on the cusp of heading to VBA... before I do though I wanted to see if anyone out there had done this and could share?

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[davedigerati]

Thank you FDibbins, but as I just registered here (after years of lurking!) I am not allowed to attach pics or workbooks yet. The instructions said after 'a few posts' I would, but in the meantime I included a link to the exact workbook you recommend (on my google Drive).

Thanks!

[FDibbins]

No, you cannot (yet) attach a pic or a link (which we dont really want anyway, but yes, you can attach a sample workbook, as per my directions.

[ChemistB]

See attached sheet for an example. The User would not need to do any maintenance.


In Sheet2, I have a list of states in col A
Col B uses an Arrayed formula (entered with CNTRL SHIFT ENTER) in B2 copied down

Please Login or Register  to view this content.

See the Name Manager for defined names "States" and "cities"

[davedigerati]

Yikes, you nailed that FAST... thank you and my hat's off to you! Took me a little time to figure out how you accomplished it and bring in all the table and named ranges I wanted to use, but got there and it is working, thank you!

Only question, is there any way to refer to a table[column] in the formula used to make the column of cities? Specifically the ROWS($B$7$B7) in this
{=IFERROR(INDEX(tblCityStates[city], SMALL(IF(tblCityStates[state]=chooseState, ROW(tblCityStates[state])-6),ROWS($B$7:$B7))),"")}

(I'm asking to make this formula easier to copy/paste int he future - just rely on tables & named ranges)

[ChemistB]

Sorry Dave, not sure what you are asking for. The column of cities in my workbook automatically narrows to the cities within a specific state as soon as a state is chosen by the user. Do you have multiple columns of cities? How is your table set up?