Named ranges and basic header rows and columns

Worksheet headers

The first sheet, called "Worksheet headers", shows you how you can use the header row and header column functionality of Resolver One to access a cell from a grid of numbers by cross-referencing between headers. The sheet contains a grid showing the monthly sales by region for an imaginary company. At the bottom, in cells A13:C15, we have a place where you can enter a month and a region, and get the appropriate sales figure.

To see how this works, first look at the "Edit Names..." dialog, which can be accessed from the "Data" menu. You will see that the cells C13:C15 have been named (from top to bottom) bigCoMonth, bigCoRegion and bigCoSales. (You can disregard the smallCo... names for now.) If you now look at the post-formulae user code (the yellow section), you will see that there is a single line like this:

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

Taking this step by step:

• bigCoMonth, bigCoRegion and bigCoSales just refer to the named cells; attaching .Value allows you to set or get the cell's value.
• workbook["Worksheet headers"] gets the worksheet called "Worksheet headers" from the workbook.
• The second set of square brackets following the worksheet reference allow us to look up a particular cell inside it the worksheet. The first parameter in these square brackets 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.

There is one caveat; these lookups can only be performed in the user code. If you put a header lookup inside a formula, you may not get the effect you expect. This is because Resolver One does not look at this kind of lookup when determining the order in which it should execute formulae, and so your lookup might be executed before the value it is looking for has been calculated. There is more discussion of this on the page Header Rows and Columns.

Worksheet headers

This worksheet shows the monthly sales for a different company. The setup is much as it was for the other worksheet, but this time, instead of setting the header row and column on the sheet, we have simply defined a named range covering all of the data - check the Edit Names... dialog again to see this. When a named range is defined, the first row and first column are automatically set as headers for that particular cell range, and so you can use them elsewhere.

Even better, if you look at cell C15, you will see that this time we were able to use the header-based addressing in a formula rather than having to drop into code. This is because Resolver One is able to tell that if a cell depends on a named range, it must depend on every cell within that range - so it knows what order to calculate everything in.

Back to Sample Spreadsheets.