Home >  Excel Solutions  > 

An Excel 2003 Tutorial:

Create Variable-Length Lists
From a Workbook Database

by Charley Kyd
(Follow this link for the Excel 2007 version.)

Kate,

Many Excel users have a similar challenge. So I'm going to make my answer as general as possible.

After you read this, you might wonder if I also made it as long as possible. That certainly wasn't my intention. This will be a long response because there will be a lot to explain.

It certainly would be possible to write a macro to do what you ask. There are at least two problems with that approach. First, most Excel users should not become programmers; they have enough to do in their real jobs.

Second, user-written macros are difficult to maintain, particularly after the people who wrote them leave. So the following solution does not use macros.

Instead, I'll show you how to use dynamic range names and conditional formatting to cause Excel tables to change automatically in response to changing data.

Depending on how you look at it, this could be good news or bad news. It's good news if you want the opportunity to learn more about some of Excel's most powerful and flexible features. It's bad news if you're looking for a one-minute solution.

Introducing the Sales Summary Report

The following figure shows a simplified version of the tracking log you sent me. I set it up in a new workbook in a worksheet I named Log.

The following two figures show where we're headed. Excel uses the data from the log to automatically generate a Sales Summary Report for any sales person whose name is entered in cell D1 of the report.

To be clear, when I entered "alyson" in cell D1, Excel displayed the table shown in the range A3:D8 for Alyson. And when I entered "peter" in cell D1, Excel displayed the table shown in the range A3:D6 for Peter.

Let's see how Excel performs this magic without using macros.

Create the Log

The first step is to create the log in a new workbook. The first figure above shows the result.

For tables like this I always include the shaded border rows shown in the figure. And I anchor all range names for the table in those empty border rows. Then, when I insert new rows of data I always do so between the shaded borders. By using this approach I always ensure that my formulas adapt correctly when we insert or delete rows in the database.

There aren't any formulas in the Log figure above. The Row numbers count downward from the value 2 in cell A4.

The text in row 2 of the Log table represents the range names that I wanted to assign. To assign that text as range names, select the range A2:E10 in the Log worksheet, and choose Insert, Name, Create, or choose Ctrl+Shift+F3. In the Create Names dialog, make sure that only Top Row is checked, then choose OK.

Begin the Report

To begin the Report table, enter and format rows 1 through 4 in the Report table shown here:

Cell C1 contains the right-aligned text "CurSalesPerson". Assign this text as the name of cell D1. To do so, select the range C1:D1; choose Insert, Name, Create, or choose Ctrl+Shift+F3; make sure that only Left Column is checked; and then choose OK.

Replace the text in cell D3 with the formula shown here for that cell:

D3:  =PROPER(CurSalesPerson)

Here, the PROPER function displays the sales person's name in upper-lower case. It makes the report better-looking, but provides no other value.

Enter the First Row Formula

We rely on Excel's MATCH function to return the row numbers that we need. This function takes the general form:

=MATCH(lookup_value, lookup_array, match_type)

Therefore, enter this formula for the cell shown:

A5:  =MATCH(CurSalesPerson,SalesPerson,0)

This formula returns the index number for the first occurrence of "peter" within the SalesPerson range in the Log table shown first in this article. The formula returns the value 2, which corresponds with the Row number shown in the Log table. (The gray border row counts as Row 1.)

(In Excel, "row number" typically refers to a spreadsheet row. Excel uses "index" to refer to the rows within a range. In reports, however, people often refer to table rows without reference to the spreadsheet row that it might be on. Here, when I refer to table rows -- which are the same as index rows -- I'll use "Row". Therefore, in the preceding figure, the 2 in cell A5 refers to Row 2 of the log table.)

The other formulas in row 5 use this index number (or "Row number") to return data from the Log table.

In cell A5, the zero value for the match_type argument tells Excel to look for an exact match. (This setting ignores the case of the text, however.) If MATCH finds the sales person's name in the SalesPerson range, it returns the Row number of the first instance of that name; otherwise it returns #N/A.

The #N/A result looks ugly in a report, and gives the false impression that we've experienced an unexpected problem. Therefore, we must change the formula to hide this result. Through Excel 2003, the way to do that is with an IF function. (Excel 2007 offers a more efficient approach.)

Using the general approach for Excel 2003, we have:

=IF(ISERROR(my formula),"", my formula)

That is, if my formula generates an error, return a blank; otherwise, return the results of my formula.

Using this approach, you should change the formula to:

A5:  =IF(ISERROR(MATCH(CurSalesPerson,SalesPerson,0)),"",
                       MATCH(CurSalesPerson,SalesPerson,0))

(I've added spaces in the second line of this formula to make it easier to read. Enter the formula in one line with no extra spaces.)

Add the Remaining Row Formulas

The formula in cell A6 is longer than the formula in cell A5. This is because the formula in cell A6 can't search the entire SalesPerson column. Instead, it must search the section of SalesPerson column that's below the instance we've already found in cell A5. To do so, the formula relies on the OFFSET function, which takes this general form:

=OFFSET(reference, rows, cols, height, width)

Here, rows and cols refer to the number of rows and columns that the new reference is offset from the specified reference. You'll see how this works in the next few paragraphs. In all cases in this example, cols will have a value of 0 and width will have a value of 1.

To build the formula for cell A6, I started with the OFFSET function then wrapped the other parts of the formula around it. Let's do the same thing here. Enter this formula for the cell shown:

A6:  =OFFSET(SalesPerson,A5,0,ROWS(SalesPerson)-A5,1)

This formula will return a #VALUE! error, but that's okay. We'll fix it in a minute.

The reason we get this error is that the formula returns a reference to more than one cell and is trying to force that result into a one-cell space. That's like trying to force five gallons of water into a one-gallon can. It doesn't work.

Even so, let's see what this formula does. The formula starts with the SalesPerson range and shifts it downwards by the number of rows in cell A5. This will allow the MATCH function to begin its search in the cell immediately below the cell where the first sales person was found.

However, by shifting the reference to the SalesPerson range downwards, we have a reference that extends beyond the bottom of our source table. We therefore must reduce the height of the range that the OFFSET returns. The new height needs to equal the number of rows in SalesPerson less the number of rows we shifted downwards, which is the value in cell A5.

To check that the OFFSET function works correctly in cell A6, select the formula in your formula bar, press Ctrl+C to copy it, then press Esc to return to the Ready mode. Now press your F5 function key, or press Ctrl+G, or choose Edit, Go to...all of which will launch the Go To dialog. Press Ctrl+V to paste the formula into the Reference edit box; then choose OK.

After you do so, Excel will select the range returned by the OFFSET function, which should be the range shown here:

The next step is to wrap the MATCH function around the OFFSET function.

A6:  =MATCH(CurSalesPerson,
         OFFSET(SalesPerson,A5,0,ROWS(SalesPerson)-A5,1)
         ,0)

Here, I've shown the formula in three lines, with the first and last line showing the additional parts that you add to the formula. Again, enter it as one line with no extra spaces.

When you enter this formula, you should see the value 4, as shown in the figure below.

Finally, for this cell, we need to wrap the formula with the IF test to protect it from the #N/A problem.

A6:  =IF(ISERROR(
       MATCH(CurSalesPerson,
          OFFSET(SalesPerson,A5,0,ROWS(SalesPerson)-A5,1),0))
        ,"",
       MATCH(CurSalesPerson,
          OFFSET(SalesPerson,A5,0,ROWS(SalesPerson)-A5,1),0)+A5)

Again, I displayed this formula in multiple lines so you could more easily see how its sections fit together. After you enter it in one line with no spaces, you should have the result for cell A6, shown here:

Copy this formula from cell A6 to the range A7:A15.

Enter the Other Formulas in the Report Table

The other formulas in the table are easy. Enter them for the cells shown:

B5:  =IF($A5="","",INDEX(ClientName,$A5))
C5:  =IF($A5="","",INDEX(Date,$A5))
D5:  =IF($A5="","",INDEX(CallType,$A5))

As you can see, all of the heavy lifting was in column A. Once we know the index number for each row of data, we simply use the INDEX function to return the data we need to our report. Of course, if the cell in column A contains a null string ("") these formulas also return a null string.

Copy the formulas in the range B5:D5 to the range B6:B15. As a general rule, it's a good idea to copy the formulas to enough rows that will allow for future growth in your log. I chose to copy to row 15, but you could copy the rows even further.

Format the Table

We use a combination of standard and conditional formatting with the table.

For the standard format, we assign borders to the entire table. To do so, select the range A5:D15. Choose Format, Cells, or choose Ctrl+1. In the Border tab, choose the border pattern shown here. Then choose OK.

To assign the conditional formatting, make sure that the range A5:D15 is still selected, and that cell A5 is the active cell. Then choose Format, Conditional Formatting. You will see this dialog:

Change the first dropdown to Formula Is, which generates this dialog:

We want the first conditional formatting to occur whenever we have blank cells in column A. That is we want it to occur whenever column A has a null string. Therefore enter...

=$A5=""

...as the formula.

Why do we use $A5 in this formula? Because the active cell is in row 5, and because there's no "$" sign in front of the 5, this reference causes each cell in the selected range to look in column A of its own row.

To see how this works, if I select cell D7 and display the Conditional Formatting dialog, the formula displays as:

=$A7=""

Again, the conditional format in this cell relies on column A of the cell's own row, but only because there was no "$" in front of the row number.

We now need to specify the format to use. To do so, choose Format in the Conditional Formatting dialog. In the Borders tab, remove all borders so that the Border setup area looks like this. Then choose OK.

With range A5:D15 still selected, and with cell A5 as the active cell, choose Add in the Conditional Formatting dialog to add a second condition. Change the first list box to Formula Is and then enter this formula:

=AND($A5<>"",$A6="")

Because the active cell is in row 5, this formula says to apply the conditional formatting if the cell in column A of the current row contains actual data, and if the next row down in column A contains no data. That is, apply this format if the current row is the last row of the table.

Again, choose Format and then define the border setting shown here.

After you choose OK, your Conditional Formatting dialog should look something like the image shown below.

When you choose OK, your table should look and work like the two Report figures shown near the top of this article.

Optional Error Test

This solution relies on our having enough formulas in the Report sheet to display all instances of the specified sales person. Therefore, it's a good idea to be alerted when we run out of room and need to copy some more formulas to display a larger Report table.

The easiest way to do so is to replace the formula in cell D3 with one that provides an error message if there aren't enough rows for the table. The error message would appear in cell D3, as shown here:

Here's the logic of the test: If the count of the sales person's name in the Log equals the count of the row numbers in the Report, return the sales person's name; otherwise return an error message.

To get the count of the sales person's name we use the COUNTIF function, which has this general form:

=COUNTIF(range,criteria)

Specifically, to count the number of times that the current sales person's name appears in the SalesPerson column of the log, we would use this formula:

D3:  =COUNTIF(SalesPerson,"="&CurSalesPerson)

The rows of data in the Report table should have exactly this number of rows. Therefore, we can construct an OFFSET formula that returns a reference to the cells in column A that should contain row numbers:

D3:  =OFFSET($A$4,1,0,COUNTIF(SalesPerson,"="&CurSalesPerson),1)

That is, beginning in cell A4, move down one row and return a reference that returns the number of rows we need. As before, the formula at this stage will return a #VALUE! error.

We can find the number of rows of data in the Report table with the COUNT function:

D3: COUNT(
        OFFSET($A$4,1,0,COUNTIF(SalesPerson,"="&CurSalesPerson),1))

This value should be equal to the COUNTIF result. If it is, we return the current salesperson's name; if it isn't, we return an error. So here's our final formula:

D3:  =IF(COUNT(
           OFFSET($A$4,1,0,COUNTIF(SalesPerson,"="&CurSalesPerson),1))
       =COUNTIF(SalesPerson,"="&CurSalesPerson),
       PROPER(CurSalesPerson),"Add rows to table!")

As always, enter the formula in one line without extra spaces.