Dynamic Dependent Dropdown List

[Jemini Jimi]

I am trying to create a Dynamic Dependent Dropdown List, one that updates as data is added to the right of the original list. There will be a different DDL on every sheet in the workbook.
I have tried many things, but I've never been able to get it to work.
If this can be accomplished with a formula that can be copied and pasted, or with VBA, that would be great.

The data Headers will always start in cell U30 and will continue to the right. The first issue I ran into was that there will be blanks and symbols in the text. (green)


A table will be used for data entry. I need the DDL in cells BD2 & BE2.

 

[Burrgogi]

You might want to read these:

Create a dynamic dependent drop down list in Excel an easy way

See how to make a dependent dropdown list in Excel by using dynamic array functions. Create multiple dropdown, make the list expandable, have the items sorted alphabetically, and more.

www.ablebits.com

Creating a drop-down list that is dependent on another drop-down list.

 

[Jemini Jimi]

Burrgogi, I've looked at this, and unfortunately, it won't work for what I'm trying to do. That shows how you can add additional dropdowns if needed.
I will have only two dropdowns in cells BD2 & BE2. The data list will expand to the right, so the DDL needs to accommodate that.

The first issue I ran into was that there will be blanks and symbols in the text. (green)

I was able to resolve this issue using this macro and formula.

How to remove special (unwanted) characters from string in Excel​

 

[Peter_SSs]

I am a little unsure just what you have after resolving the first issue but to resolve the issue of an expanding list with Data Validation, IF you have the relatively new TRIMRANGE function & associated Trim Refs notation you can do this.

Here is my initial list that may expand to the right and/or down. In my Data validation I have limited that to columns U:AZ and rows 30:100 but that can be altered if required.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Jemini Jimi.xlsm
	U	V	W	X	Y	Z	AA
30	Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
31	Item 11	Item 21	Item 31	Item 41	Item 51	Item 61
32	Item 12	Item 22		Item 42	Item 52	Item 62
33	Item 13	Item 23		Item 43		Item 63
34	Item 14			Item 44
35				Item 45
36
Sheet1

For Data validation in the table ..

Jemini Jimi.xlsm
	BA	BB	BC	BD	BE	BF	BG	BH
1	Hdr1	Hdr2	Hdr3	Section	Activity	Hdr6	Hdr7	Hdr8
2	a			Header 2	Item 22
3	b			Header 5
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
BD2:BD3	List	=$U$30:.$AZ$30
BE2:BE3	List	=INDEX($U$31:$AZ$100,0,XMATCH(BD2,U$30:.AZ$30))

Notice in the DV formulas there is one instance each where there is a colon followed by a period ":." in a range reference. This is the Trim Refs notation.

Here is an example of the 'Section' drop-down

.. and the subsequent 'Activity' drop-down

IF you do not have the TRIMRANGE/Trim Refs option yet then post back for an alternative if it looks like what I have described/shown is what you are after.

 

[Jemini Jimi]

Peter, this works perfectly!
I did not need to remove blank spaces or special characters, as I was told was necessary for DDL to work.
Thank you so much, I learned a lot.
BTW I will install XL2BB

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.