Data records are entered into one work sheet and then save to another (the database). Each record is one row.
Normally, I would use the data validation - custom and put the formula below.
=COUNTIF($A$1:$A$50,A1)=1
But you cannot reference another worksheet using this.
So I need some formula which says:
Check if the data in cell B3(worksheet1) is the same as the data in col a3:a20 (worksheet2), if the data in B3(worksheet1) then bring up message "Duplicate Record".
Thanks,
Dave32
Named Ranges can be used to make Validation work across sheets.
If you have a named range Name: myRange RefersTo: =Sheet2!$A$3:$A$30,
then the validation criteria =(COUNTIF(myRange,A1)=1) will work in Sheet1!A1
You could also set the validation to list style =myRange.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Brilliant,
This works and I get a result of TRUE or FALSE. I have tried to use the formula in the custom Data Validation, but I keep getting my erro message "Duplicate Record" this is not correct for the text I am entering
Dave32
Last edited by Dave32; 04-14-2008 at 10:34 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks