Validation

[Dick]

Sheet1 has a list of part numbers in Column A.
On Sheet2 column A I'd like for when the user types in a part number
and it does not match part number from Sheet1, an error message shows
invalid part number and prevents the user from entering that part
number.
Thanks so much for any help!
Excel 2003

 

[Dave Peterson]

You could try Data Validation.

See Debra Dalgleish's site for more info:
http://contextures.com/xlDataVal01.html

 

[Dave Peterson]

Ps.

Data|Validation is not too robust. It can be broken very easily -- just a
simple paste will break it!

 

[Dick]

Ps.

Data|Validation is not too robust.  It can be broken very easily -- just a
simple paste will break it!

Thanks for your help Dave. But I have no idea how to write the formula
needed in data, validation. Is it a lookup, match? Something like that?

 

[Dick]

You could try Data Validation.

See Debra Dalgleish's site for more info:http://contextures.com/xlDataVal01.html

Went to the link you suggested. Was a great help! Got what I needed
from there. Thanks So Much!!!

 

[MeowSayTongue]

Not if the cells are locked?

Ps.

Data|Validation is not too robust. It can be broken very easily -- just a
simple paste will break it!

 

[Dave Peterson]

If the cell is locked, then xl2003 won't let me change the value in the cell at all.

If I unlock the cell, I can paste anything I want into that cell.

But even if that isn't true, worksheet protection is easily broken, too.

 

[GS]

Dick expressed precisely :

Thanks for your help Dave. But I have no idea how to write the formula
needed in data, validation. Is it a lookup, match? Something like that?

Hi Dick,

Validation would use a 'List', and the source for that list should a
dynamic (global) defined name range. This will force users to only
enter/select items in your list.

To do this, the list must occupy contiguous cells (no empty rows
between 1st & last part num) in columnA on Sheet1...

In the defined name dialog:
In the name box type: PartNumberList
In the RefersTo box type: =OFFSET(Sheet1!$A$1,0,0,COUNTA($A:$A),1)

Select columnA on Sheet2.
In the Validation dialog:
In the data type list select: List
In the source box type: =PartNumberList
Fill in message text as desired...