Lightweight Forms Database with Excel

A forms database is useful when you have a lot of similar information that needs to be consistently formatted. A practical example would be a simple invoice database which contains our invoice records and a professional format. This can be accomplished with any number of tools, but I'm going to show how it can be done with Excel using the VLOOKUP function and Named Ranges. The Big Idea® here is that both features have some quirks that can make implementation a pain, so I'm going to show two workarounds that I've had success with: a Template Row and a Lookup Tab.

Creating the Template

To get started quickly, I just used a template included with my version of Excel, the appropriately named 'Blue Invoice'. Below I populated it with with some fake information for demonstration purposes. For the record, I don't have a goat business, and no goats were harmed in the making of this blog. A few claimed to be offended, but I got the impression they thought just about everything was baaaad.

The Data Setup

Our lightweight Excel forms database starts out as a simple table with columns that map to the fields we need to display in the form. For the record, column J is setup as a product of H * I, but we could always overwrite this as needed. Note that I add an "Invoice" column at the beginning of the table which will serve as the unique identifier of each row. This does not necessarily have to be displayed on the template, but it is critical for our VLOOKUP function to work. The INV00001 pattern is arbitrary, but whatever pattern is used needs to create unique values for each row. From experience, it is also handy for the unique identifier to behave predictably when sorting.

The VLOOKUP function takes a search term, a range, and a column number as inputs. It traverses the first column in the range vertically (thus, "V" lookup), looking for a match based on the search term. If a match is found, it then looks across that row to the column number, and returns that data. To make it easier to work with the VLOOKUP function, I create two named ranges. The first only covers the first column, which I call InvoiceSelector. We'll use this range to hookup to a drop down list to select the row we want to display in the form.

The second named range covers the entire table, which I've called InvoiceLookup. We will use the selection from our drop down list and this named range to power our VLOOKUP function.

The Template Row

By this point, you may be wondering why row 4 looks a little different. Row 4 can be thought of as a prototype row. It has the default tax rate, formatting, and calculation in column J. The trick to adding new data is to right click on the last row, copy, and then right click again, and insert copied rows above the last row.

The point of this admittedly odd sequence has to deal with how Excel treats named ranges. Below, you can see that when a row is inserted above the last row of a range, the range expands. Note that the range now includes row 5. However, when a row is inserted below, Excel does not automatically expand the range to include it. Therefore, our VLOOKUP function would find the data in row 5, but not row 6. Lastly, the reason why I start the name of the template row with 'zzCopy' is to force it to sort to the bottom when sorting column A in A-Z order. At the end of the day, this is all very simple stuff, but many brain cells died to bring you this information.

The next step is to actually link our data with our form. Our drop down list is created by applying List validation to our drop down cell and making the Source =InvoiceSelector. Now this drop-down list can power our VLOOKUP search.

The Lookup Tab

Now, we could put VLOOKUP functions directly into our template, but this would require us to 'hard code' the column number associated with each field. So if we ever wanted to add or remove a column, we'd have to go back and manually update all impacted fields. A much better method is to create a separate lookup tab, whose only role in life is to display the data in an easily accessible format.

You can see what's happening when you display formulas. The first row points to the contents of our drop down cell. Rows 2 thru 11 use that selector field $B$1, the Invoice Lookup, and the column number in column C. The idea is two-fold: First, we are not going to hard code the column numbers into our VLOOKUP functions, and second, we are going to link our template cells to THESE cells in the lookup tab, rather than embedding VLOOKUPs with hard coded column numbers all over our template.

The invoice template with formulas displayed looks like below.

Other than some basic formatting needed for the date, the links are simple references to the lookup tab data. Setting it up is generally as simple as clicking the cell, typing '=' and then clicking the target cell on the lookup tab.

I mentioned earlier that adding columns to VLOOKUP tables can be tricky when using hard coded column mappings, but the lookup tab solves that. If we added a column to our table, we would simply insert a row in the same corresponding position on the lookup tab. Excel is smart enough to adjust the impacted cell references in the template for us. So as long as the the lookup cells point to the right columns, we're all set! To accomplish this update, we would label the row in column A with our added column name, copy our VLOOKUP formula into column B, and adjust the column numbers in column C to match the correct column sequence of the table.

Wrap Up

Using the concepts of Template Rows and Lookup Tabs means lightweight forms databases can be deployed quickly, maintained easily, and adjusted on the fly using Excel. There are more sophisticated tools out there, but sometimes it's hard to know exactly what you need. So even where a simple Excel database isn't ideal, you can start basic and learn more about what you really need by doing. The key, as usual, is to get started!

By: Matt Wright 11/22/2020

Tags: technical, excel, tips, tricks