Named ranges and basic header rows and columns

This topic demonstrates how to perform lookups using...

The example workbook contains two sheets, one for each of the above.

How to open the example workbook

Do one of the following:

  • From Windows, select Start, Resolver One, Samples, Named ranges and basic header rows and columns
  • Download the example from the Resolver Exchange.

Worksheet header lookup

The Worksheet headers worksheet demonstrates the use of a header row and column for looking up a cell.

The grid contains the monthly sales by region for an imaginary company. At the bottom, in cells A13:C15, you can enter a month and a region, and get the appropriate sales figure.

To see how this works...

  1. Select Data, Edit Names to open the Edit Names window.

    Cells C13:C15 have been named (from top to bottom) bigCoMonth, bigCoRegion and bigCoSales. (You can disregard smallCoRange for now.)

  2. Look at the Post-formulae user code (the yellow section).

    It contains a single line of code—see below.

About the code

A single line of code performs the worksheet header lookup:

bigCoSales.Value = workbook["Worksheet headers"][bigCoRegion.Value, bigCoMonth.Value].Value

Here's how it works:

bigCoMonth, bigCoRegion and bigCoSales
Refers to the named cells.
.Value
Sets or gets the cell's value.
workbook["Worksheet headers"]
Gets the worksheet called Worksheet headers from the workbook.
[bigCoRegion.Value, bigCoMonth.Value].
Looks up a particular cell inside the worksheet. The first parameter specifies the cell's column, which can be a number, a reference like "A", "B", "C", or, if a header row is defined, a value from that row. The second parameter specifies the cell's row in much the same manner. In this case, we are using the values of the bigCoRegion and bigCoMonth cells to get the value of another cell.

This kind of "semantic lookup" allows you to access data in your spreadsheets much more safely than you can in a traditional spreadsheet environment. However, it only works in the user code. For using header lookups in the formulae, use named ranges (see below).

Note

Header lookups may produce unexpected results inside formulae because Resolver One does not assess them when determining the formula execution order. This means that the lookup can be executed before the target value has been calculated. There is more discussion of this in Header Rows and Columns.

Named Range header lookup

The CellRange headers worksheet sets out another company's monthly sales. It's much like the other worksheet, but instead of a sheet header row and column, it has a Named Range covering all of the data. The first row and first column of this range are automatically set as headers.

To see how this works...

  1. Select Data, Edit Names to open the Edit Names window.

    There is just one named range for this sheet, smallCoRange, which contains all of the data.

  2. Look at cell C15.

    This contains the formula =smallCoRange[C14, C13]. Just like the user code for the other worksheet, this uses the contents of the cells where you enter the region and the month as indexes to extract a value, in this case from the named range rather than the entire worksheet. Because this lookup is into a named range rather than a worksheet, it works fine in a formula.

Note

If a formula in a cell depends on a named range, Resolver One recalculates every cell in that range before evaluating the formula.

Comments