Data Validation Trouble

[jdc2k2]

I have created a dynamic data validation list in a worksheet, using nested IF statements and named ranges. The list shows up just fine, however, I am not getting an error when I enter invalid data. I've checked to make sure the error alert is enabled and recreated the data validation a few times using the INDIRECT statement (some references I found seemed to suggest this might be necessary).

To clarify, I want to have the dynamic validation list displayed based on the value of another cell, and I also need it to restrict the data input to that validation list. I'm using Excel 2007. The worksheet is not shared or protected at this time.

Here's a summarized version of the current validation list formula.

=IF($A1="1",List1,IF($A1="2",List2,IF($A1="3",List3,IF($A1="4",List4,IF($A1="5",List5,DefaultList)))))

I've done the normal digging around with forum/Google searches and help file reading, but so far I haven't found an answer to this issue. Hopefully I didn't overlook something simple and am not wasting people's time.

Thanks for your time and any assistance you can provide!

 

[mallard1976]

Perhaps you tried this already, but this really should work:

1. enter your validation formula into, say, cell B1:

=IF($A1="1","List1",IF($A1="2","List2",IF($A1="3","List3",IF($A1="4","List4",IF($A1="5","List5","DefaultList")))))

By the way, do you need to use double-quotes for numbers ?

2. select cell where your dropdowns are to appear and choose Data -> Data Validation

3. Allow List
4. Source "=INDIRECT($B$1)" (without quotes)
5. Tick "Show error alert after invalid data is entered"

When A1 is empty, dropdown should be empty and no values accepted.
Otherwise, dropdown should be populated and only values from dropdown accepted.

 

[jdc2k2]

That worked!

I guess when I used the indirect statement before, I was still using it in the formula box inside the validation list options, not an actual formula in a worksheet cell.

Is there anyway to do this as just a formula inside the data validation options, instead of having to add the column for the formula in the worksheet?

On the double quotes around the numbers, they are actually proper named strings in my formula, I just summarized the list 1-5 for the sake of simplicity.