Help with data validation formula error

[boim]

Ok, I'm stumped with an error while trying to add this formula to a list type data validation:

Formula: "=IF(LEN(Prev)>0,StatusID,"")"
Prev is a Name with formula =RC[-1]
and StatusID is a Name range =dbStatus[StatusID]

This is the error in Excel: "The list source must be a delimited list, ... or column."
In VBA, it gives out Error 1004.

The strange thing is that if I add this data validation to some cells in a test workbook, it works fine.

Any thoughts?

 

[boim]

Found what caused the problem:

Turns out the add of data validation is successful when Prev initially contains something. When content of Prev is later deleted, data validation still applies, thus the whole thing works. (The validation basically says if Prev contains something show the list of valid entry, otherwise nothing.)

However, when I tried to do this programmatically, the column where Prev refers to may have BLANKs/empty; the add fails, ergo the error.

Still, shouldn't the formula work in the first place regardless whether Prev contains something or not? Afterall the validation still works when Prev is empty after its content is deleted.

Any comments on this?