Help with more flexible dependent data validation

[newqueen]

Dear All,

I want to adapt a dependent data validation so I could use it for the quick lookup of price of products – a request that we receive numerous times in a day. My Product/Price listing or database with live data, and which runs into hundreds of rows, takes the same format as the sample Region/Customers listing that I am using (attached) as example of what I need help with.

In the second sheet named ValidationLists, there is a list of Regions and their associated customers.

a) In the first sheet named ValidationSample, when I select a Region from the Dropdown list in Cell B2, only the Region’s customers will be shown when the dropdown in cell C2 is selected.

b) Also after a Customer is selected, the Region dropdown shows only that customer’s region.

c) And when a customer is selected from Cell C2 dropdown, it will not be possible to select another Region in Cell B2, UNTIL the content of Cell C2 is cleared and the cell made blank!

All of the above work very well.

However, because I want to use such a set up for the purpose of looking up the price of products (using my ‘products/price’ live data), I’d like some adjustments made to Cells B2 and C2 respectively – some flexibility, if it is possible.

I therefore need help, please, with the following:

1. I don’t want to keep clearing the contents of C2 manually before I could make a subsequent selection from the Region dropdown in B2.

2. As in (a) above, when I select a Region from the dropdown list in Cell B2, only the Region’s customers will be shown when the dropdown in cell C2 is selected. Fine!

3. However, unlike in ( c ) above, when a customer is selected from cell C2 dropdown, I should be able to select another Region in Cell B2 – BUT when such a subsequent selection is made from B2 dropdown, the content of cell C2 should automatically be cleared – with the cell showing blank. At that point, when the drop down arrow in cell C2 is clicked, it will show the regions customers.

No. 3 above is the flexibility that I need help with.

The necessary Excel ‘Offset, Index and Match’ formulas for the basic functioning of ‘dependent data validation’ are shown under ‘Name Manager’ and ‘Data Validation’. I am hoping that there could be a tweaking of the formulas, or an additional formula that will help me to achieve No. 3 above.

I would prefer an Excel formula that could do that for me. However, in the absence of any such formula, I’ll be equally happy for a VBA code that will help me to achieve No. 3 above.

I’ll be very grateful for any help, please.

Newqueen

[newqueen]

Could someone kindly help, please. Thanks.