Conditional Expression Question

T

tristatefab

Hello,

I have a ship log I am doing for work, the columns are as follows....

Cust. Name Order # Part # Qty Shipped Price Per Unit
Invoice #

Anyways, the problem I have now is the person that fills out the shi
log will sometimes ship off the same work order different times durin
the day and often he will not mark it for me on the ship log, so I wil
use two invoice numbers for the same order # and I have to go throug
and find out exactly where he shipped the same order # and then I hav
to white out and renumber all my invoices on the log (HUGE HASSLE) I a
tired of it. I am creating a spreadsheet to eliminate thi
inconvenience. So I tried to make a formula that will automatically "
out" the invoice # cell of duplicate work order #'s so I do not enter
invoice number twice for the same order #. I tried this for exampl
=IF(B6:B7=B8,"--------","") it flags an error saying something is wron
with the formula. I want it to check cells from B6:B172 to see if th
work order # entered in the order # cell already exists and if so, pu
--------- in the invoice # so I do not put a new invoice number in fo
that order#. It seems as if it will not let me check if a numbe
already exists in a range of cells? Does anyone have any suggestions o
how to make this work or need some more clarification on what I a
trying to do? I hope this makes sense. I can send you the excel file
am working from...

Thanks in advance..
 
S

SteveG

You can limit the entries using Data Validation. Say your range o
invoice #'s are in A1:A2000 (this includes an allotment of empty cell
for future input of invoice numbers). In cell A1 click on, Data an
select Validation. On the settings tab, change the Allow option t
Custom. In the formula bar type the following formula:

=COUNTIF($A$1:$A$2000,A1)>1=FALSE

Click OK.

Copy Cell A1. Highlight the rest of your range A2:A2000. Edit, Past
Special, Validation. OK.

This will only allow unique entries to be entered in that range.

Does that help?

Stev
 
I

intruder9

If you are unsure of how to do this send me your example and I will se
it up for you
 
Top