(repost) Data Validation and Named range.

[rob nobel]

Sorry to repost this again, but would like some help with this if possible.
I have this as the named range, entitled "MissCodeList"
=Miss!$D$4:$D$15,Miss!$D$21:$D$24,Miss!$K$4,Miss!$K$6
and have Data validation set to custom with "=MissCodeList" in the formula
box.
On closing the validation dialog, it says, "The formula currently evaluates
to an error. Do you wish to continue? I suspect what's causing this is that
I'm also
trying to avoid certain cells within the columns .....because even if I
remove Miss!$K$4,Miss!$K$6 from the named range it still won't work.
So, using a named range in Data Validation doesn't seem to work if the above
conditions apply. Is there a way round this???
Rob

 

[Jim]

Rob, if you want, send the file to me, removing the usual garbage from my
address. Please include a clear statement of what you need.

 

[Stephen Bullen]

Hi Rob,

So, using a named range in Data Validation doesn't seem to work if the above
conditions apply. Is there a way round this???

To use a named range for a validation list, it must be a single area, so you'd
have to create the list in a separate block of cells and use that as the
validation source.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[rob nobel]

Thanks Stephen. Regretably I reckon you're right!
What a nuisance that is. I hope future versions will improve that feature.
Rob