Dynamic Data Validation

Here is a concrete example based on your picture. In the attached workbook I have used the same row/columns as you provided in your picture. I have populated only the subset of cells needed to demonstrate the solution. As follows:

DisposalTypeDropdown.png

I used a "helper" range in K2:K6. For simplicity this should be in the same set of rows as your first table. In K2 copied down to K6:

Formula:

Please Login or Register  to view this content.

This simply takes the "Disposal Type" value if "Location" is WestTest or blank otherwise.
B16:B19 are the values to be used in your dropdown. In B16 and then copied down:

Formula:

Please Login or Register  to view this content.

This is an array formula and must be committed with CTRL-SHIFT-ENTER. It takes the helper column values, removes duplicates and and shuffles values upwards to eliminate any blank rows in the helper column. Copy the B16 formula down for as many different "Disposal Types" as you have. Note - this group of cells can be relocated wherever you like.

Create a named range WestTestDropdown defined as follows:

Formula:

Please Login or Register  to view this content.

This will be used to define your H9 dropdown list values. Its sole purpose is to eliminate blank entries at the end of the dropdown list that would occur if the B16:B19 range was used directly.

Finally in the Data Validation List, for "Source" type =WestTestDropdown

The attached workbook implements the above. Hopefully there is enough info here to allow you to implement something similar in your real workbook.

Let me know how it goes.