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
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
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?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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).
Why not just make the named range dynamic so that if you add items it expands to include them?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Another advantage of a dynamic named range is that it can be on a different sheet.
see attached
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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,
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
Why not just set it up so that when you close the workbook it closes without alerts and doesn't save changes?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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
But you were wanting a UDF solution which would require macro's to be enabled too.......??
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Yep, the workbook wouldn't work - that's expected. But it also wouldn't prompt to save.
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?
Peace of mind is found in some of the strangest places.