While we’re on the topic of helpful applications and using technology to help boring yet fundamental reference department functions, here’s another bonus for using Excel: tracking usage of Reference materials.

It’s one of those things that we know we ought to do, but too often gets left behind in the mass of things requiring our time and energy. And if we DO decide to track the usage of our Reference materials, we end up doing something like putting colored stickers or tally marks inside the back cover. I would like to offer a simple solution. It still requires DOING it, but it makes it quite a bit easier. Especially come reporting time.

Most of our libraries use computers for checking books in and out. That means that most of us have books that contain bar codes. Even if the books don’t circulate, that’s part of standard book processing for many libraries. What may or may not be available is a bar code reader. If you don’t have an extra one around that you can assign to your Reference Desk, there are some simple ones that just plug into a computer that don’t cost an extravagant amount. Bar Code Discount Warehouse is a good place to start looking. That’s where we gotten most of ours. Prices vary, but you don’t have to have portable ones that you can take into the stacks. The model we use is great and is just a fraction of the price of some of them. Be sure to ask about discounts for libraries, too, just in case.

Anyway, what I did was create a simple Excel spreadsheet. Every time your bar code reader scans a code, it puts the number into the cell and goes down to the cell below. It’s just like typing in the number and hitting [enter]. Only it’s quicker and there are no typos. :-)

From this list your Systems person should be able to generate a report that adds call numbers, authors, and titles to the spreadsheet. And there you are, a report of the books that have been used. But that’s just the start!

By itself, this is no big deal. It’s just a list of numbers, leading to a list of books. There are two bits of code that can easily be added to your worksheet which will add value and convenience to your worksheet:

  • One snippet of code will make Excel automatically move the Active Cell to the first blank one. That means that when you open the spreadsheet, it’s all ready to go. You just scan, save, and close, without having to arrow down to the bottom of your list.
  • The other bit of code adds a timestamp to the second column, so whenever you scan a book, it puts the date and time in the cell next to the bar code. Now you can generate reports that relate to usage over time, or that document days of the week or which books were used which month. Much more handy than just a list of numbers or titles!

Now, about adding the code to Excel. It’s easier than you think. You just right-click on the tab with the name of the worksheet (usually Sheet1 unless you’ve changed it) and select View Code. Type in the following or copy and paste it from this blog post.

Private Sub Workbook_Open()
Worksheets("Sheet1").Activate
Columns("A:A").Select
Range("A1").End(xlDown).Offset(1, 0).Select
End Sub

What that specifically does is, when the workbook is opened, activates the first sheet (you can rename that however you like), selects the first column, and then goes down one cell past the last one containing data. The only catch is that you can’t have any empty cells in Column A at the top of your list. Just a title and then your data. Otherwise it will stop at row 2 or whatever one is left blank.

The second bit of code is what enters the timestamp.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub

This one basically says that if data is entered in column A, go to the adjacent cell in column B and put “Now” in it. You can format column B to whatever date, time, or combination of both you find useful.

In summary: Here are the steps to getting this all set up.

  1. Create a blank Excel spreadsheet with just a title and column headers at the top.
  2. Right-click the worksheet tab and select View Code.
  3. Paste the two bits of code into the window that comes up. (Excel will automatically separate them with a line.)
  4. Close the window by clicking on the X in the corner or typing Alt-Q for quit.
  5. Save the workbook and exit Excel.

Voila! There you are. You’re all set to start scanning books and tracking what is getting used in your Reference Collection. I hope this works for some of you and helps you use technology as a useful tool instead of a hurdle or just “something else to learn.” Technology is supposed to HELP us, not get in the way, and this Excel spreadsheet will do just that.