Hi All,

I am looking to create Data Validation that will look at multiple cells to create a combined list from two values. It is probably most easily explained with an example:

So I would like the Validation in Cell B4 to look at two Named Ranges, as defined in cells A2 & B2 (i.e. "ApplePrices" and "OrangePrices"). I would like a single combined list to be available in the drop-down being presented in B4.

Elsewhere in the book/sheet (Let's say D1:E20) I have got a series of values. These values are split into Named Ranges, e.g. D1:D5 = ApplePrices, D6:D10 = OrangePrices, E1:E5 = PearPrices, D11:D20 = BananaPrices, etc.

I would like a Drop down in cell B4 that would refer to what is being held in A2 & B2 (In this case ApplePrices & OrangePrices) and use that as a reference to build the single drop down for B4.

The Data Validation in cell B4 needs to be populated based on both A2 & B2 - i.e. as the sheet currently stands and referring back to the named ranges, I would need a drop-down in cell B4 that is made up of both named ranges (i.e. the values held in D1:D5 & D6:D10). However, if I was to adjust the value in B2 to be "PearPrices" the drop-down list for B4 would adjust to now contain D1:D5 & E1:E5. Finally, if I adjusted the contents of cell A2 to "BananaPrices", the drop-down in B4 would now be constituted of the values held in D11:D20 & E1:E5.



Hopefully I have explained this well enough!

Thank you for your time,

Rich