MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Display an alert if an entered value already exists


Posted by sid on September 12, 2001 5:14 PM

I have a list of numbers on a sheet formed from 3 columns eg d1 = a1&b1&c1
On another sheet I want to input a similar arrangent but on completion of the 3rd cell, to check this total number against the d column list and if it already exists, display an alert and empty the 3rd cell.
I have been playing about with vlookup in a separate column and trying to display a message based on a true/false value in that cell.
Any help on the best way to do this would be greatly appreciated.


Posted by Jaime on September 12, 2001 9:29 PM

Maybe Data Validation might help.
Got Data then Validation.If the setting's tab select Custom in the first List box then in the Formula box type :
=COUNTIF(sheet1!d1:d4,D1)=0
Whenever u type in a value that is in existence in the first page, u will get an error message that tell you that value is not valid.

This is the easiest solution I can think of. W/O using VB.

Posted by sid on September 13, 2001 3:08 AM

Data validation will not reference to other worksheets


This is the sort of thing I am trying to do, but data validation does not work across different worksheets. As I have over 50 sheets requiring this check I need some sort of cross sheet checking.

Posted by lenze on September 13, 2001 6:48 AM

Re: Data validation will not reference to other worksheets

Jaime: Istead of using the cell references in your Data Validation, use a named range instead.
In Sid's formula, replace "sheet1!d1:d4" with a named range. This will work across worksheets.

Posted by lenze on September 13, 2001 6:50 AM

Correction:

Sorry: I reversed your names.

Posted by sid on September 13, 2001 5:54 PM

Many thanx guys - works fine

cheers