PDA

View Full Version : Cell Validation List from a UDF



Adamski
01-08-2010, 09:58 AM
Hello,

Is it possible to use a UDF for data validation? If so how. It seems to consider everything a named range - unless I'm doing it wrong. I thought a function returning a 1D range, or an array of strings would be possible.

Cheers

xld
01-08-2010, 10:17 AM
Not exactly clear as to what you want the UDF to do. DV basically tests for a true or false condition, so do you want to pass the cell to the UDF and do that work there?

Adamski
01-09-2010, 09:17 AM
At the moment I have Validation set up to list values from a Named Range in a drop down for the user to pick from. I want the same functionality but without the need for the Named Range - the list items instead supplied form a udf (not a static list).

lucas
01-09-2010, 09:47 AM
Why not just make the named range dynamic so that if you add items it expands to include them?

lucas
01-09-2010, 10:02 AM
Another advantage of a dynamic named range is that it can be on a different sheet.


see attached

Adamski
01-09-2010, 11:27 AM
Thanks, but the reason I wanted to see if it could be done with a function is that my NamedRange (which is working well) needs to use the Index function. This causes the workbook to be Dirty on opening and so prompts to save changes even when none are made.

If List data must come from either a range or a static list, I could execute a macro OnChange to set the NamedRange (without needing the Index function).

Just thought I could maybe do it with a UDF in Validation instead. It's Excel 2003 by the way.

Cheers,

xld
01-09-2010, 12:41 PM
Index is not volatile, so it should not make the workbook volatile. unless that is you are using a form such as A$2:INDEX(A$2:A$20,some_rownum) , in which case it does seem to becme volatile.

But I still don't get where the UDF will get these values from to pass back, and anyway, I don't believe you can use UDFs in DV.

Adamski
01-09-2010, 02:28 PM
Yes, I am using Index as the second part of a range reference which is flagged as dirty when the workbook is opened. The UDF would do the same thing as my current NamedRange - it would return the same data but without needing the Index function.

Anyway, I also don't think it is possible to use a UDF in Data Validation.

lucas
01-09-2010, 02:30 PM
Why not just set it up so that when you close the workbook it closes without alerts and doesn't save changes?

Adamski
01-09-2010, 02:34 PM
Yeah, it's all fine so long as it is opened with macros enabled. It's only a minor issue I was hoping to fix - not important really.

Cheers

lucas
01-09-2010, 02:36 PM
But you were wanting a UDF solution which would require macro's to be enabled too.......??

Adamski
01-09-2010, 02:39 PM
Yep, the workbook wouldn't work - that's expected. But it also wouldn't prompt to save.

austenr
01-13-2010, 04:12 PM
I like Lucas example but how do you get to the refers to in data validation in 2007? I don't see it...Also, can you dynamically sort a new entry to the range?