Odd validation issue

[haydonlawrence]

Hi all,

I'm having an issue with context-based validation.

Basically I have a spreadsheet where I want the data validation drop-down list in column B to be based on the selected value from the drop down in column A.

So, for example, if the drop down in column A gives you the values X, Y, and Z to choose from, and you choose value X, then a different set of values should be available to be chosen from the drop-down in column B than if you had chosen value Y or value Z.

I have accomplished this using dummy-data, using an IF statement (a bit longwinded but it worked) and named ranges in the validation list settings. But for some reason, it is not working when I use the actual data (names of workstreams and their sub-workstreams) and I'm given the error "The list source must be a delimited list, or reference to single row or column".

The formula I used for the dummy data was: =IF(A1="X",XList,(IF(A1="Y",YList,(IF(A1="Z",ZList,""))))) where XList YList and ZList were the named ranges for the values applicable to values X Y and Z respectively (e.g. the lists of subworkstreams for workstreams X Y and Z respectively).

Can anyone think of any reason why this would suddenly no longer work with real (as opposed to dummy) data? I'm utterly confused.

Many thanks!

 

[Derek Brown]

Hello and welcome to The Board.
Not the exact answer to your question but perhaps you will find the following a better way to do it:
MrExcel's Learn Excel 383 - Dependent Validation - YouTube
To find other MrExcel podcasts on this subject, search for "mrexcel dependent data validation" on YouTube.