Hello,
I am trying to develop a visitor log for the building I work at on campus. The log will have nine columns and roughly 100 rows. Each column is a different type of data that must be entered by the security guard: Date, Name, ID Type, ID Number, Affiliation, Staff Visited, Badge # Issued, Arrival Time, and Departure Time. Currently I am stuck on a problem within the log.
To stop a problem of guards signing out multiple people to one badge I want a color change or message to come up that symbolizes the badge is already checked out. This is fairly simple with the basic logic following along the lines of:
IF Badge # cell is filled and IF Arrival Time cell is filled but Departure Time is blank, THEN check Badge # with other Badge #s that meet same criteria (Arrival Time filled, and Departure Time blank). Where I run into the issue is when I want to compare one checked out badge to all of the other checked out badges. I am basically making another column with will set all checked-out badges to their numbers, and all checked in badges to "0", since there is no "0" badge. But, If I compare to columns to look for duplicates, I need to exclude the cell being checked. I guess I can better explain this as G2:G102 are the badge numbers, and M2:M102 are the second badge number column made to check for duplicates. If I am checking G2 against the M2:M102, M2 will always be the same, because it is a duplicate of G2.
I am trying to use a COUNTIF() function to do the comparison. Is there anyway to exclude a single cell from the function?
Thank you for your time and please let me know if you can use more clarification.
New Visitor Log - Copy.xlsx
Bookmarks