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

Hi
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and follow the wizard to open the upload window.

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi there,

I have created an example workbook that should hopefully explain what I am getting at. I have done this to match the example written in my original post.

Thanks for your time,

Rich

Attached Files

Two Named Ranges, One Drop Down.xls (26.0 KB, 62 views)

Nevermind - I have managed to build a dynamic list elsewhere in the spreadsheet using an Array formula now.

Hi Pecoflyer,

Can you please re-share the sample document you have made? i have the same issue and need to get a list of specific data based on 'Client Name' & Document Type

Thanks!