Seal Report Help

Hi
I am working a spreadsheet for my job to track seal numbers that are placed on certain reference points. I am lost as to how to proceed. I have attached a sample tracking report for your review.

Column B - This is where a check mark or an "x" is placed to confirm that a seal that was taken off matches a seal that was put on and it has to match the reference point too.

Seal Ref Point - This defines where the seal location is. I need to use numbers ranging from 1-12 and this will represent an oil tank. I also need to track other reference points range from "A" to "Z".

Seal On and Seal Off - this is the seal number that was placed on or taken off

Date - Date of activity

By: initials of the person doing the transaction

Reason: This is why the seal was taken off or placed on the reference point.

The normal activity is to have a seal off and seal on. For most "Reasons" the off/on transaction can be done on a single line. For tank changes ( Fill/Sell) it takes two lines to process the transactions. I have highlighted some examples.

I need help with three areas
1) How do I match seal numbers when a seal is taken off. Example Seal # 44 on reference point B was taken off. This needs to match the seal #44 when it was placed on that reference point and then a check mark or an "x" needs to be placed column A.

2) How do I get a warning box to pop up when the seal numbers do not match?

3) I plan on using a data entre sheet and have the data flow to this report.

any guidance you can offer will be greatly appreciated.

Thanks
Ted

You might use Conditional formatting as a warning and an IF formula to place the X... see attached file.

Originally Posted by dangelor

You might use Conditional formatting as a warning and an IF formula to place the X... see attached file.

Thanks for you help. Your solution does not quite work. example, if I was record enter at reference point 2 on the next available line, I can type in 23 in the off column (should be 22) and the forumla does not find the error.

Try these formulas...

Copy into cell B5 and then copy down
=IF(ISNA(MATCH(D5,$E4:E$5,0)),"", "X")

Conditional format cells D5:D44 with
=AND(ISNUMBER(D5),ISNA(MATCH(D5,$E$5:E4,0)))

See attached revised file