See the attached.
This a macro attached to Sheet5 which records the address of the Active Cell in D1.
The formula for the Validation_List now looks at the address in D1 and dynamically creates a list for whatever is entered in a DV in Column B.
The drawback is you have to clear the B cell entry (Use Delete on keyboard) for the macro to determine the address.
in E2 and copy down
=IF(ISNUMBER(SEARCH(INDIRECT($D$1),F2)),MAX($E$1:E1)+1,0)
I also changed the DV
=IF(B2=" ",Full_List,validation_list)
"Full_List" is named range (E2;E68)
You get the "Full_list" if the B cell is empty, otherwise you get the shortened "Validation_List"
Hope this helps.
Bookmarks