DATA VALIDATION

BillHarris

New Member
Joined
Aug 5, 2002
Messages
14
I am trying to use the Data /Validation function to show a warning message when a duplicate number is input into the current cell(i.e F4), which matches a number which could have been input into a cell within the range (F8:F5000).

Please could you advise if such a function is possible in Data validation, and if so the correct syntax to use.

Alternatively is there any other function that could achieve the desired result.

Thanks
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
You could setup a formula within another part of the spreadsheet that vlookups the number in F4, if it finds it then enter a 0 in the box, if not then enter a one. This can be done using the if(iserror( statements.

Have a macro that works upon changing that call (F4), you may need help for this - I dont know how to do this.

That macro could look at the vlookup result, if it is a 1 then it does nothing, if it is a 0 then put a msgbox up, and if necessary clear F4 out.

This should work in theory, but I am unsure of the code to do so.

Hope it helps.
stuart
 
Upvote 0
=COUNTIF($F$8:$F$5000,F4)=0

If the value entered in F4 is in the range F8:F5000, you will recieve the warning message
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top