Prevent a duplicate value between 2 fields

[anthgav]

I have 2 fields and I don't want to allow the same value in either field. For example if the number 1 exists in 'Field A' I don't want to allow number 1 to be entered in 'Field B'. I've spent a considerable amount of time trying to find a solution for this but I'm struggling to work it out. I've worked out how to set up a composite index to prevent a pair of fields from matching but this not what I require.

Does anyone know how to apply this?

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Incidentally would you add your location to your profile please. It often helps when regional settings or date formats need to be considered.

[Ravisca2003]

I have 2 fields and I don't want to allow the same value in either field. For example if the number 1 exists in 'Field A' I don't want to allow number 1 to be entered in 'Field B'. I've spent a considerable amount of time trying to find a solution for this but I'm struggling to work it out. I've worked out how to set up a composite index to prevent a pair of fields from matching but this not what I require.

Does anyone know how to apply this?

Hi Anthgav,

I tried and it works with below way. Feel free to share further if you need anything more. It goes like this

Lets say your first value is in Cell "B2" and second Value is in "B3"

Step 1 Select Cell B3
Step 2 Goto Data Tab>Data Validation ( Under Data Tools ) > data validation > Custom.
Under this Put this FormulA = =B2<>B3
Step 3 . Now select Cell b2
Step 4 Goto Data Tab>Data Validation ( Under Data Tools ) > data validation > Custom.
Under this Put this FormulA = =B2<>B3

Now for both cells the validation logic is in place. Try and it will post you an error.

Hope this helps.

regards
RK