Printer Friendly

Lookup tables to speed data entry and reduce errors.

Like other busy professionals, medical technologists tend to view repetitive data handling chores with disdain, if not despair. As a result, jobs accumulate. Microcomputers are superb tools for reducing the stockpile.

Among the assignments easily lightened by computers in the clinical laboratory are special projects and monthly batch jobs that are not handled by the laboratory information system (LIS). Such tasks may require functions that are unavailable in the LIS, yet be too small to warrant the effort of creating a unique computer program.

This article will describe one small job that used to be a nuisance at my laboratory but is now a breeze. Our health maintenance organization (HMO) bills many labs and other cost centers throughout Hawaii for various materials ordered in bulk by the main lab, where I work. These supplies include microbiology media plates for the satellite labs, Chlamydia swabs for the emergency room, and the like. At the end of each month, we are responsible for notifying the accounting department regarding who should be billed for what.

The small program I created with the lookup function in Lotus 1-2-3 (Lotus Development Corp., Cambridge, Mass.) performs offline calculations for those recharges in an electronic ledger. Other laboratorians might find the program useful for performing many similar tasks, such as organizing test result reports to be collected and sent to a central database elsewhere in the institution.

The lookup program facilitates rapid data entry. At the same time, it minimizes re-keying of frequently needed data--numerical codes for the various labs, for example--thus reducing the user's opportunity to insert new typographical errors. The program will work with any version of Lotus that contains the @VLOOKUP function.

The worksheet consists of two sections: the data entry form (Figure I), which we print out each month and send to the accounting department as a report, and the vertical lookup table (Figure II).

* Template. The data sheet template is shown in columns A through F of Figure I. (G and H are left blank as spacer columns.) In column A, "Clinic code," the operator enters the code number for the laboratory to which a given item is to be charged and then presses <ENTER>. The vertical lookup table, embedded in the program, immediately begins to work behind the scenes, retrieving and posting the description associated with each code entered--in this case, the name and eight-digit identification number of the cost center (column B). Because the four-digit prefix of the recharging area rarely changes--it's usually the main lab, coded as 8007-(column C)--I usually fill in that column in advance.

The operator continues working from left to right, filling in the number of the recharging cost center--that is, the section of the main lab that is to be reimbursed for the item (column D), the HMO's expense code number for the supplies in question (column E), and the dollar amount (column F). Items represented in column E include prepared media, QC re-agents and supplies, and other materials.

* Lookup table. A vertical lookup table is a small database that is easily called up from any cell in the worksheet by means of the @VLOOKUP function of Lotus 1-2-3. In the recharge ledger format we use, the lookup table is conveniently located directly to the right of the data entry form (Figure I, columns I and J). There it remains, readily available for periodic updates.

* Setting up shop. Create the data entry form. Using the Worksheet-Column-Set Width command, set each column width to the number of characters indicated in the small box labeled "Column width settings," located below the screen in Figure II.

Enter a formula in cell B10 and copy it down the column through B22 (or as far as you need). Using the Range-Format-Currency command, format column F, which will indicate dollar amounts, as currency.

Now build the vertical lookup table. Leave I10 and J10 blank. In I11, type a list of as many sequential numbers as needed to describe the items or places to be identified (in our case, about 20 codes representing our far-flung labs and some other hospital departments).

Set column J wide enough to accommodate all descriptions to be entered. I always use 30 characters. Now type the descriptions themselves.

Note that 16, 17, and 22 read "undefined code, please check." This notation acts as a safety net. Entries beside 16 and 17 represent error messages warning the operator that those two numbers are not assigned to any locations at this time. If 16 and 17 were left out of the lookup table altogether, and someone accidentally type one of them, the program would look for the code requested, fail to find it, and identify the previous one instead. Placing "undefined code, please check" at the end of the list (cell J32) creates a catchall for all mistakenly keyed unassigned numbers over 21.

With the Range-Name-Create command from the main menu, assign a range name to the table. Choose the name "table" and highlight only the table itself, which consists of the area from I10 to J32. Include the two blank cells, I10 and J10.

* Test the worksheet. Position the cursor in cell A10 and type the number 4. The description entered in the lookup table (here, South Shore Lab) should appear. Move the cursor down one cell and type "10"; its correlate, Maui Lab, should pop up. You are ready to create your report.

* Technical note. The @VLOOKUP function nested into the cells of column B works in the following way. The command @VLOOKUP (item, range, column offset) defines the function. Item represents the value to be looked up; in our case, the cost center code number. Range identifies the range in which to find the value--here, the area named "table." Column offset will cause data to be retrieved from a designated column as soon as "value for item" is located in the table.

Entering the number 4 in cell A10, for example, triggers the formula in B10 to identify 4 as "item." The cursor moves to the range named "table" and moves down column I until it finds the value 4. At that point the @VLOOKUP function stops and moves one column to the right until it locates our description (that is the cost center), which it carries to cell B10. The @VLOOKUP function remains invisibly in the cell, "under" the display.

* Advantages. Lookup tables enable the user to process a substantial amount of data rapidly, expending minimal effort and making fewer errors than would be possible otherwise. Because database and other functions within the spreadsheet program can be used to sort data after entry, it is unnecessary to presort source documents. An additional beauty of this simple system is the ease with which it can be extended and revised.

The short example provided in this article demonstrates that even a simple lookup table can expedite tedious clerical tasks. Create your own lookup tables and save yourself a surprising amount of time. You may even find yourself looking forward to taking care of previously daunting chores.
COPYRIGHT 1991 Nelson Publishing
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 1991 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Author:Cody, Brian E.
Publication:Medical Laboratory Observer
Date:Jun 1, 1991
Previous Article:Risk taking: a supervisory imperative.
Next Article:A physician glut by 2000?

Related Articles
Taking the 'byte' out of method evaluation number crunching.
Lotus 1-2-3 worksheet for fibrinogen calibration curves.
EZchip's NP-1c network processor is certified by the Tolly Group for passing IPv6, IPv4 and MPLS industry benchmarks.
Double-teaming in Excel: spreadsheets now can solve tougher calculations.
The keys to calibration: machine tool 3D volumetric calibration replaces 1D linear calibration.

Terms of use | Copyright © 2017 Farlex, Inc. | Feedback | For webmasters