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

Thanks in advance...


--
tristatefab
------------------------------------------------------------------------
tristatefab's Profile: http://www.excelforum.com/member.php...o&userid=32647
View this thread: http://www.excelforum.com/showthread...hreadid=524518

You can limit the entries using Data Validation. Say your range of
invoice #'s are in A1:A2000 (this includes an allotment of empty cells
for future input of invoice numbers). In cell A1 click on, Data and
select Validation. On the settings tab, change the Allow option to
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, Paste
Special, Validation. OK.

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

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=524518

You can check here for some ideas
http://www.cpearson.com/excel/duplic...tingDuplicates
Also can't you just sort the sheet at the end of the day, this would
put all the same numbers right after each other?


--
intruder9
------------------------------------------------------------------------
intruder9's Profile: http://www.excelforum.com/member.php...o&userid=30107
View this thread: http://www.excelforum.com/showthread...hreadid=524518

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


--
intruder9
------------------------------------------------------------------------
intruder9's Profile: http://www.excelforum.com/member.php...o&userid=30107
View this thread: http://www.excelforum.com/showthread...hreadid=524518

Thank You Very Much. This did help me out a lot!


--
tristatefab
------------------------------------------------------------------------
tristatefab's Profile: http://www.excelforum.com/member.php...o&userid=32647
View this thread: http://www.excelforum.com/showthread...hreadid=524518