Data Validation Excel 2003

[Mike]

I have to update a workbook for a customer regarding PCs that my company
ships out. Two of the fields that we record are ASSET TAG and SERIAL NUMBER.
These 2 pieces of data are already linked prior to shipment. For example
SN 1234 is linked to asset ABCD. I'd like to do a validation on this data as
I enter it to ensure there are no typos, or multiple assets assigned to 1 SN.
Which Excel Formula should i use for this? ANy thoughts

 

[JLatham]

You want to verify that either/both of the data items, SN and asset ID don't
exist elsewhere in the list. This assumes your list is all on one sheet and
that you're adding new entries at the bottom of the list.

Add formulas at the end of the rows. Begin with Row 2. We will assume that
your serial numbers are in column A and your Asset IDs are in column B.
In one empty cell in row 2 enter this formula (to check for duplicate serial
#s)
=COUNTIF(A$1:A1,A2)
In another empty cell in row 2 enter this formula (check for duplicate Asset
IDs)
=COUNTIF(B$1:B1,B2)

These cells will show 0 (zero) if the current row's entry has not been used
before, they will show 1 or another higher number if the entry has been used
before.

A fancier way to do the same thing. Replace 1st formula above with this:
=IF(COUNTIF(A$1:A1,A2)>0,"Duplicated","")
and replace 2nd formula with this:
=IF(COUNTIF(B$1:B1,B2)>0,"Duplicated","")

these will leave blank cells where the current SN/Asset is unique, and
display the word "Duplicated" if it has been used before.

Whichever method you choose, just keep copying that formula down the sheet
(see Help for Fill data/formulas for fast ways to do it) along with your new
entries as you make them.

 

[Mike]

Thank You for yuor response, but I think I wasn't clear enough in what I am
trying to do. Currently I have a data set in excel which lists all of the SN
/ Asset relationships, and this relationship must always be the same, so I am
looking for a way to refernce this master list to confirm that the correct
data has been enterred. FOr example I need to make sure SN 1234 alwyas
matches Asset ABCD no matter when it is used, and if someone tried to enter
SN 1234 with ASSET DEFG it would prompt me with an error. ANy thought on
this?

Thanks,

Mike

 

[Roger Govier]

Hi

If you have all of the SN's and Assets listed already, you could create
2 named lists.
Assuming the SN's are in column A and Assets in column B.
Insert>Name>Define>Name> serial .> Refers to A1:A100
Repeat using Name List > Refers A1:B100

On your sheet, apply Data Validation to the cells where you input SN's
Data>Validation>use dropdown to select List> =serial
Let's say this is a range of cells from D120
In cell E1 enter
=VLOOKUP(D1,LIST,2,0)

That way, the serial number entered will always be a valid one, and the
user will not be entering the Asset number.

 

[JLatham]

Yes, I misinterpreted your need and Roger has offered what is probably the
best/most efficient way to do what you REALLY wanted to do. Perhaps there
may still be some use for what I offered back in the source area of your
SN/Asset lists to ensure that your source lists are actually valid?