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
