Advanced validation lists with name range formulas

[XLScottW]

Hi

I have been browsing this forum for a few weeks but haven't had a need to post. However I have a tricky problem I haven't been able to solve but I'm confident there are some real XL gurus out there for whom this will be a piece of cake

I need to use data validation for several columns of input cells where the choice in a previous call will change/limit the selection available in subsequent cells.

In the example JPEG posted below columns B,C,D will be on a hidden data source validation sheet. The user input is in F,G,H. If "division A" is selected than the choice of "Business" should reduce from business 1, 2, 3 & 4 to just business 1 and business 2. If "Business 1" is then selected then the "asset" choices should reduce to just assets 1, 2,3 & 4.

If this needs further clarification please let me know. I'm pretty sure this should be doable without code using just name range formulas feeding the validation.

Cheers,
Scott

This is cross-posted on the mrexcel board
http://www.mrexcel.com/board2/viewtopic.php?t=265600

[Bryan Hessey]

Hi

I have been browsing this forum for a few weeks but haven't had a need to post. However I have a tricky problem I haven't been able to solve but I'm confident there are some real XL gurus out there for whom this will be a piece of cake

I need to use data validation for several columns of input cells where the choice in a previous call will change/limit the selection available in subsequent cells.

In the example JPEG posted below columns B,C,D will be on a hidden data source validation sheet. The user input is in F,G,H. If "division A" is selected than the choice of "Business" should reduce from business 1, 2, 3 & 4 to just business 1 and business 2. If "Business 1" is then selected then the "asset" choices should reduce to just assets 1, 2,3 & 4.

If this needs further clarification please let me know. I'm pretty sure this should be doable without code using just name range formulas feeding the validation.

Cheers,
Scott

Hi,

I think that http://www.contextures.com/xlDataVal02.html should provide you with that aswer.

---

[BigBas]

If you post a sample of your file in zip format, we can help you better.

[XLScottW]

I came up with a solution that is flexible (ie no INDIRECT formulas requiring pre-specified name ranges) and should work for any number of interdependencies and size of array.

The only problem to watch out for is the character limit for validation and name range formulas.