Dynamically Expanding Named Ranges in Excel

Saturday, 29 January 2011 00:10 Last Updated on Saturday, 30 July 2011 04:11 Written by Steve Powell

Creating combo or list boxes are easy if the items in your lists don't change between revisions (for example, a list of months in the year). For most people, however, lists tend to be dynamic and in constant evolution. If you spend a lot of time updating each list, you'll inevitably forget to update one at a critical juncture, probably right before an important client presentation, knowing Murphy's Law! In order to automate a somewhat repetitive and manual task, as well as to minimize human error, you can use named ranges with formulas.

Assume you have a worksheet with company details as shown in Exhibit 1a, and a worksheet with account data as shown in Exhibit 1b.

Exhibit 1a: Sample company data (companies sheet)

Exhibit 1b: Sample account data (financials sheet)

For each company there is one row on the companies sheet, and multiple rows on the financials sheet. The two sheets share the ticker column. Since the companies sheet has one row per company it is a good source for a distinct list of companies to use in a list box that will go on the interactive page we'll create.

Now you want to create a named range that includes the names of the companies that we'll display in the list box. In Excel 2003, you can go Insert->Name->Define. In Excel 2007, use the named range manager. Enter the following formula into the "Refers To" field: =OFFSET(Companies!B2,0,0,COUNTA(Companies!B:B)-1,1), and name the range "Tickers".

We'll also want to create a second named range that includes the tickers. Define another named range with the "Refers To" field set to: =OFFSET(Companies!A2,0,0,COUNTA(Companies!A:A)-1,1), and name the range "CompanyNames". This second range will be used to look up the ticker of the company selected which can be used to pull out data on that company from the financials sheet.

Exhibit 2: Creating a named range

The OFFSET formula can be used to adjust the location and size of a range. In usage above, we're starting with Sheet1!B2 (the top cell of the company name column). We don't want to adjust the position of the range (hence the two zeros) but we do want to adjust the rows to include all the companies (i.e. we're adjusting the height of the range). We do this by counting the number of items in the B column with COUNTA(Sheet1!B:B), minus one for the title ("Name"), and stretch the range to that many rows. The ticker named range works the same way.

Now we can add our list box. Create a sheet named "Main" which will be the sheet we display to the end user. From the form button bar, select the list box and place it on a sheet. Then right click the list box and select properties. Enter "CompanyNames" into the "Input Range" field. Enter "$B$2" for the "Cell Link" field. When a selection is made the value of B2 will change to the index of the item selected (so if you click on the first one it will be 1). Change the value of A1 to "Index" so it will be more obvious what B2 is. Columns A and B can be hidden later so the users of the workbook don't need to know about it.

Exhibit 3: Formatting the list box.

Now that we know the index, getting the ticker is trivial. In Cell B2 use this formula: =INDEX(Tickers,B1). This will give us the value of the cell in Tickers that is on the same row as the selected company name. The INDEX function has additional parameters that are optional such as column but Tickers is only one column wide so we don?t need to supply a column. Enter a label ?Selected ticker? in A2 so that we can remember what B2 is.

Exhibit 4: The list box in action.

Suppose one of our objectives is to display the data from the financials sheet. In order to do that we need to be able to pick out values for a specific ticker, account and time period. The problem is that the keys to the data on the financials page are in different columns and VLOOKUP can only use the first column. We add another column named ?key? to overcome this. The formula for A2 is =B2&"_"&C2&"_"&D2&"_"&E2, and we drag the formula all the way down for the rest of the column.

Now that we have the ticker of the selected company in F3, and have added the key column to the financials data, we can use formulas that reference the ticker to provide dynamic content as seen in exhibit 5.

Exhibit 5: We use the ticker to make this table dynamic.

The formulas for the table are fairly basic:

The revenue formula can be copy and pasted to COGS and for the remaining years the formula can be copied/dragged across. For Revenue we?re combining the elements of the key (ticker, year, time period type, and account) and looking up the matching row from the data page. The currency formula is similar because we?re looking up one of the values (assuming they all use the same currency). The growth formula can also be dragged across for the additional years.