PDA

View Full Version : Validation Issue (Excel 2003)



Sir Babydum GBE
08-10-2007, 03:17 AM
Hi again.

I know how to put a custom validation in a cell so that you can't input into it unless another cell has an input. I also know how to put dropdowns into a cell.

But is there a way of changing the validation settings so that I can have both?

I.e. I want a dropdown list to appear - but only if the user has completed a different cell first.

Any ideas?

unmarkedhelicopter
08-10-2007, 04:30 AM
The only way I can think of is code, have your second cell set to prevent entry untill 1st cell is filled, then have change event re-write validation on second cell, similarly clear second cell if first emptied.

rory
08-10-2007, 04:32 AM
I'd do it the other way round personally - have the validation list set on the second cell and simply clear the contents if the first cell is blank.
It's not possible without code.

Sir Babydum GBE
08-10-2007, 07:06 AM
Thanks guys, I've thought of another way in the meantime:

I can have a dynamic range which serves as the list source for the dropdown. Then I can make sure the list items are formulae that return empty strings if certain cells are not completed by the user. So the dropdown button will be there, but it'll be empty.

Now I've just got to think which way I want to play it - yours or mine.

Either way thanks for the suggestions.

Bob Phillips
08-10-2007, 07:50 AM
ASsuming the list is in M1:M10, put a value in the marker cell, I will use F1.

In the DV, use a type of list and a formula of

=IF(F1<>"",M1:M10)

You can then clear F1 and it will be no list.

rory
08-10-2007, 07:57 AM
Very cunning - I'll be filing that away for future reference. Thanks.

Sir Babydum GBE
08-10-2007, 08:07 AM
Wow - it works! (Not that I doubt you Mr X - it's just that I tried that before and got a message sayin I couldn't do it - but didn't think to put a value in the other cell first. How daft that it brings up an error in the first place though - seeing as it can do it anyway)

Thanks!

(I reckon this should go in the kb or articles, cos validation issues are quite common I should imagine)

Sir Babydum GBE
08-10-2007, 08:09 AM
Very cunning - I'll be filing that away for future reference. Thanks.more cunning than a vast number of weasels holding a convention on cunningology (apparently).

Bob Phillips
08-10-2007, 08:15 AM
Wow - it works! (Not that I doubt you Mr X - it's just that I tried that before and got a message sayin I couldn't do it - but didn't think to put a value in the other cell first. How daft that it brings up an error in the first place though - seeing as it can do it anyway)

Yes you did ... doubt me!

It is not daft actually, it is Excel being (really) helpful. DV evaluates the formual as entered to ensure integrity. That way you don't get DV run time errors. If F1 is empty, it wants to take the false condition, which is not a valid list, hence the error. By pre-loading F1, you force it to take the true condition, and everything is fine. Once you have the DV setup, it is too late for it to complain.


(I reckon this should go in the kb or articles, cos validation issues are quite common I should imagine)

I don't do KBs or articles, there is plenty of detail in the archives as to why not.