Automatic counter based on changes to a cell (inventory usage tracking)

[librarianonabudget]

I am a librarian interested in tracking book checkouts over time. I only need to track how often an item has been used, not when or by whom it was used. My library is too small to justify the purchase of an ILS (circulation software), so I am looking for a simpler solution using what I already have: Microsoft Office. My workforce is not skilled with technology, so I need something as simple and with as little opportunity for human error as possible.

Whatever I end up with will ideally serve two functions: 1) to track who currently has a book and 2) to record statistics over time. With that in mind, what I envision is the following:

Column A = Title
Column B = Author
Column C = Shelf Location

Column E = Patron Name [will contain text only while book is checked out]
Column F = Checkout Date [will contain a date only while book is checked out]
Column G = Total Checkout Count [contains a value which automatically updates based on the corresponding cell in Column E or F]

In other words, columns E and F will be cleared when a book is successfully returned in good condition.

Is there some kind of (counter?) function that will increase a value by 1 in Column G when a referenced cell (in Column E or F) contains any data at all, but then does not decrease the value when the data is erased? And then increases the count again when new data is entered into the same cell?

Alternatively, I have considered adding an additional column (let's say Column D) with cells in a format that offers an A/B option (such as a dropdown menu or a checkbox representing "checked out" or "not checked out"). The theoretical counter in Column G would increase each time the corresponding cell in Column D is switched from option B to option A (but preferably not from option A to option B).

Is this even possible? If so, how would I go about doing this?

If my envisioned scenario is not possible, is there a different way to solve my problem using the tools available to me?

Thank you all in advance for brainstorming with me.

 

[Yongle]

The obvious way would be something like this

- click on column A to open UserForm_Checkout (if column E is blank) OR Userform_Returned (if column E contains a name)
- UserForm_Checkout would ask user for Patron Name
- clicking OK would then put the name in column E, put the current date in column F and increase the count in column G
- Userform_Returned would clear columns E & F
- consider clicking on either columns E or F to open a UserForm_Edit allowing the user to amend the values without affecting the count (correct errors)

If you need help setting this up, come back with your comments to make sure that it is created to work in the way you want it to work
- think about how you want it to work (Buttons, Click on cells etc)
- the VBA to do this is not complex but it needs setting up to make it intuitive for your users
- VBA can be used to prevent the user amending the value in column G (could allow simple password access if required)