Validity: how to require unique values? (Excel compatibility)

[JohnAbJs]

I have a very similar problem so I thought I could post it here. I have an Excel spreadsheet moving to Open Office but have some users that will stay in Excel. So I want the same formula (or keep the spreadsheet in Excel). I'm using the formula =(COUNTIF($F:$G,G5)=1 where G5 is the current cell being worked on and the formula is in the Data Validity area. What this is supposed to do (and it does it in Excel), is to check for only 1 number out of the list in $F:$G. If the number already exists, it is supposed to give an error. In Calc it works very strangely. It will accept any number in the first cell (right or wrong) and not accept (give an error like it should when the number is in the list) any number in any of the other cells. That is, no error on the first cell and errors on all other cells.
Now maybe there is an easier way to check for duplicate entries (and I would be willing to go that way). Or maybe I'm doing something wrong and Excel is just letting me get away with it. But like I said, it does work in Excel.
Looking for a way to fix this. And I did check that in Calc it has a semicolon instead of a comma in the formula.
Thanks for any help. I will try to include a test spreadsheet (Sheet 1 has names and Sheet 2 has the formula entered through Data Validity). Try entering a number between 1 and 8 in cells G5 to G8 in Sheet 2.

[acknak]

[Moderator note: Moved to new topic]
I think it's quite a different topic, even if it is similar. Also, it seems the other topic was not finished. It's very confusing to have two discussions under one roof.

I'm not sure how to do this, but I think it is an interesting question.

Looking at your sheet, I see two things that prevent this from working. First, Calc does not seem to support a validity test where any number is accepted as long as the validity formula is true. At least I don't see any way to set that up in the Data > Validity dialog.

Second, Calc seems to follow a model where the validity test is applied before the number is actually entered into the cell; Excel (I guess) enters the number in the cell, then evaluates the validity formula. That allows Excel to use a validity test that includes the cell receiving the entry, whereas Calc cannot (as far as I can see).

It looks to me like you'll need some other way to check the values if you need the same sheet to work in both. Hopefully someone will prove me wrong