Pulling Data Validation Dropdown Options from Another Sheet (Not Dependent Dropdown)

Hello -

I've spent hours and hours trying to get this to work, but still no luck (although I have learned a lot about Excel in general).


My Goal is to add data validation to each cell, to allow the user to choose one of the Valid options (on another sheet) from a dropdown list. The paired column may or not be on the other sheet, and may be in any order. I got conditional formatting to color code for this scenario, now I'm trying to get dropdowns working...

Example: under Cars, Audi is not on the Valid Values list for Cars, so I need a dropdown to allow the user to select Ford, Kia, or Chevy.
(I'd like to avoid named ranges, if possible)
I've already added placeholder data validations to each cell; I just need the magic formula!

The attached workbook includes all of my notes and work so far...

Thank you in advance to any kind soul who can help me figure this out!

Hi there,

As far as I'm aware, you can use a specified range for data validation only if the range is located on the same worksheet as the cells for which data validation is being used.

If you want to use a range from a different worksheet, the data validation range must be specified as a Named Range.

I've used the above approach to provide data validation for the "Cars" cells (Data worksheet, Column A) so that you can see what I'm talking about.

I note that you'd rather not use Named Ranges, but I don't know of any way to avoid them in this instance.

Hope this helps/clarifies.

Regards,

Greg M

Hi Metoo7 -

I truly appreciate your help and input! However, your solution incorporates a dependent dropdown approach, which doesn't work in this case.

On the Valid Values sheet, I need each category to be it's own header, with the valid values below each header.

Can you think of any other way of accomplishing this?



Again, thank you so much!