Why Data Validation is not working?

[xluser2007]

Hi All,

I can't understand why this Data validation is not working.

In A4 I have the value '2008', with the name FIN_year.

Also A1 = FIN_year.

I want to validate A1 to only keep the value of FIN_year.

So in Data Validation I set in:
Settings:
Allow: Custom
Formula: =INDIRECT(FIN_year) [I also tried just '=FIN_year').

In Error Alert: I ticked the "Show error alert after invalid data is entered"
Style: Stop
Title: Error, wrong Data is entered
Error message: This value is equal to FIN_year

Then clicked OK.

However when I try to overwrite the value in A1, it allows me to put in invalid values without any error warning.

Could anyone kindly explain how to correct this problem.

regards,

[Bob Phillips]

You have to return a formula that evaluates to TRUE.

By using =FIN-year, this evaluates to 2008, and Excel treats any non-zero value as TRUE, so whatever you put in there will pass the test.

You need to test that A1 equals the FIN-year value, i.e.

=A1=FIN_year

[Simon Lloyd]

Bob, you will be dismayed to find this has been cross posted here!

[xluser2007]

Hi xld/ Simon,

Firstly, xld, thank you for your solution and explanation. That is what I was mainly after.

As for cross posting, I sincerely apologose. I just read through that ken Puls link in Simon's regarding cross posting in firums.

rest assured that I hadn't read this detail before and will link henceforth.

Again thank you both for your help.

I hope from my past posts you will understand that I sincerely apprecaite it.

regards

[Simon Lloyd]

Xluser, thanks for the consideration!

[Bob Phillips]

xluser2007,

I am glad your problem is solved. Unfortunately you got caught at the tail of a spate of cross-postings, and one that especially bugged me whereby the OP was running the threads on both forums in parallel, presumably to get as many options as possible so that he/she could pick and choose.

As Simon said, we do this for free, and to my mind it is hugely disrespectful to people who give their time and their expertise to not even inform them that it is cross-posted.

I happen to think this forum is far better than MrExcel, Mrexcel gets fast responses but usually to realtively simple questions. Here, I feel we tend to get questions that develop more, and are usually more complex (in rquirement even if not solution).

But bottom line, we cannot stop cross-posting. If peple wish to cross-post, so be it, but at the very least let us know. And be aware, that when told that a question is cross-posted, I for one will back away. I give far too much time already, I certainly do not need to spend time on a problem when someone else may have already solved it, or to go chasing to see what responses are proffered elsewhere.