EXCEL VBA - Creating a dynamic dropdown based on a cell range and a string [closed]

Question

Ok, so I currently have a dropdown that is populated from a range on a separate sheet.

I want to be able to decrease the values in the dropdown by only adding values that have a text value from another cell within each values text.

for example: Cell A1 says 'table' Cell A2 is the dropdown that pulls in the values from the range The range has 'table','large table','stool'

Currently the dropdown would bring in all of the values. In this instance I would want it to bring just 'table' and 'large table' as they both have 'table' in them.

an example of my file can be found here http://www.filedropper.com/example_1

Please help.. I'm tearing my hair out!

Thanks Mike

Answer 1

This is a more simplified answer.

First group the items in the sheet 'Items' in a helper column, say Column 'C' using the formula (for C2) and fill down.

=IFERROR(RIGHT($A2,LEN($A2)-FIND(" ",$A2)),$A2)

Now in the sheet 'Choose Items', for cell B2, use the following as the validation list's source -

=OFFSET(Items!$A$1,MATCH('Choose Items'!$A2,Items!$C$2:$C$10,0),,COUNTIF(Items!$C$2:$C$10,'Choose Items'!$A2),)

You can fill down to extend the validation to other rows of Column 'B'

In case your data in the sheet 'Items' needs to be extended to rows beyond 10, appropriately change the offset function's scope of the match and the countif functions to encompass the entire data (after having the grouping in column 'C' as discussed earlier).

Make sure the list in sheet 'Items' is sorted on the helper column (Ascending or Descending), which is column 'C' in this case.