Conditional Drop Down lists - with dynamically changing values

[papplaszlodaniel]

Hi dear all,

I face a challenge what I don't seem to be able to pass.

I have a separate Excel (let's call it Workbook_1) with the details of contracts with different companies. One company can have multiple contracts.
In a new Excel (let's call it Workbook_2), I would need two columns with drop down lists, but the second should depend on the first.

E.g. in Workbook_2:

Sheet1: contains the data (transferred from Workbook_1 with PIVOT)
​

Sheet2:​

• 
  [*=1]Column A - I can pick a company's name (let's say Company_X) from a drop down list. (this part is easy-peasy)
  [*=1]Column B - I should see now only this chosen company's contracts' numbers in a drop down list.

I found that trick when you name the cells and use the =INDIRECT() function.

But here comes the tricky part.

The list of a given company's contracts is not static, the list grows by time.
Even more, new companies can get in the company list as well.


Currently I use PIVOT to get the necessary data from Workbook_1, and I can refresh it any time I need an updated list.


By chance, do you have any idea how to deal with this situation?

 

[papplaszlodaniel]

Ahh a bit more complicated as we'd need to set the validation dynamically.

If you copy and paste cell A2 in Sheet1 and paste it as many times in to the rows of column A the following code works. Delete all the code behind Sheet1 and paste this:

(...)

Notice that on the DATA sheet the columns are being filled with the results. Hope this helps. Was a real brain workout!

Haha, I'm happy I could provide a brain workout.

Thanks a million! It works flawlessly! Basically the only limitation of this solution would be the maximum number of columns, but that's much more than enough.

 

[gallen]

No problem. As long as it works to your needs.