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