Excel 2013 Data Validation criteria problem

By josephking ·
Tags: Software
Help please,

I am constructing a spreadsheet to do a shipping validation function. I have created a spreadsheet to verify loads of material being shipped. I want to validate column F to column B and Column F to itself. Column B contains the serial numbers of the pallets of materials that are being shipped starting at B7 and ending on or before B46. This information comes from a csv import from our warehousing software. We start scanning the pallets to ship into column F starting at F7 and ending on of before ***. I want to validate the data being scanned into column F against the data in column B and I need it to error if it does not match. Also I want to use data validation at the same time to validate that the data being scanned into column F is not being duplicated (they scanned the same pallet twice) and if it is duplicated (scanned twice) it errors. The error message I have entered into Data Validation is "This pallet does not belong on this load or has been scanned twice." I can get either one of the criteria to work individually but cannot seem to get the syntax correct to get but conditions to work together.

To check data from column F to B I am using the following formula =COUNTIF($B$7:$B$46,F7)=1 in a custom Data Validation from F7 to ***

In column F to check for duplicates I am using the following formula =COUNTIF($F$7:$F$46,F7)=1 in a custom Data Validation from F7 to ***

Either of these work individually but I cannot come up with the correct syntax to combine these.

Any help would be appreciated,

Joe K.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Share your knowledge

Related Discussions

Related Forums