Validate a date column against a lookupfields additional column

Question

In a list you can create a lookup column which queries data from an other list, the fun part is that you can add "additional columns" which results in some sort of ghost column which is added to the dispform.aspx and the views. Pretty neat!

Now I would like to make a validation, my list (listA) consists of [Title=text], [Project=lookup] and [StartDate=date], the project column gets the data from a 2nd list (listB), I also add an additional column from that list [listB:EndDate].

When I want to make following validation, =[StartDate]>[listB:EndDate], I get an error that the column does not exist, which is true, it does not psychically exist in listA, BUT, because of the additional column it does appear on my list. So why can't I make a validation using that column?

Any idea's?

Answer 1

It's an age old issue with SharePoint that apparently hasn't been resolved in 2010: you cannot reference a lookup column in a calculated column. EndUserSharePoint.com did a lengthy series on calculated columnns. Another suggestion I've seen is to do the calculation on the client side using JavaScript: http://www.graphicalwonder.com/?p=252.

I understand your scenario is a little different as you are wanting to create a validation rule rather than a calculated column, but I suspect the answer is the same.