Header Rows and Columns

Header rows and columns are a way of allowing you to reference data in your worksheets, or in ranges within your worksheets, by using meaningful names like "Sales" instead of obscure cell references like "B12". Using them in conjunction with Named Ranges can make your spreadsheet self-documenting and resilient to change.

This works by letting you say that a particular row contains the headers for all of the columns in a worksheet or range, and similarly that a particular column contains headers for the rows. You can then use the values from the headers instead of the column identifier (say, "Sales" instead of "D") or the row identifier ("European division" instead of "12").

This page documents how they can be used; there is also an example file packaged with Resolver One that uses them: Named ranges and basic header rows and columns.

Header Rows and Columns on Worksheets

You set the header row or column on a worksheet by selecting a complete row or column and then picking the appropriate menu item from the Worksheet menu on the main menu bar. You can also set them from your code, using the HeaderRow and HeaderCol properties on the worksheets.

Once they are set, in the user code you can use the values from the headers to address rows, columns or cells:

• workbook["Sales breakdown"].Cols["D"] might become workbook["Sales breakdown"].Cols["Sales"]
• workbook["Sales breakdown"].Rows[12] might become workbook["Sales breakdown"].Rows["European division"]
• workbook["Sales breakdown"].Cells.D12 might become workbook["Sales breakdown"].Cells["Sales", "European division"]

Similarly, once you have a Row or a Col object from a worksheet, you can use the worksheet's header to access fields within that Row or Col; for example, if the header row contains columns of 'Name', 'Street Address', 'City' and 'Londoner', and you want populate the 'Londoner' column with 'Yes' or 'No' depending on whether people are London-based, you might put the following code into your pre-formulae user code:

sheet1 = workbook["Sheet1"]
for row in sheet1.Rows:
     if row["City"]== "London":
        row["Londoner"] = "Yes"
     else:
        row["Londoner"] = "No"

If you have two or more cells in a header row (or column) containing the same value, a yellow cross appears in each affected cell to warn you. It is possible to have the same header title in a row and a column but not twice in a header row or twice in a column.

You can use header rows and columns in formulae to index into a worksheet. This looks like:

=<'Price List'>['Fish', 'Price']

This formula looks up the 'Price' of 'Fish' in the 'Price List' worksheet. You can only reference the whole worksheet like this from another worksheet. You couldn't use this formula in the 'Price List' worksheet itself as this would be a cycle.

Header Rows and Columns in Cell Ranges

By default, all Named Ranges you define in Resolver One are created with their first row and column set as headers. You can modify this from code (not from the user interface) by setting the HeaderRow and HeaderCol properties on the range, just as with worksheets.

CellRange objects that you create from your own code can have header rows and columns set, either by passing in true as the value of the createDefaultHeaders parameter in the constructor, or by setting the properties.

Once the header row and column are set on a CellRange, you can use them from code just as you can with the header rows on a worksheet. Even better, for headers on named ranges, you can also safely use them from formulae! This is because Resolver One recognises that if you reference a named range from within a formula, it must evaluate every formula in that range before it evaluates your formula - so the values in the range will already be calculated when your formulae is evaluated.

Skipping Header Rows and Columns in Iteration

Often you want to iterate over the rows or columns in a worksheet or cell range, and perform an operation on every row or column except for the headers.

Both cell ranges and worksheets provide ContentRows and ContentCols iterators. These will automatically skip the headers for you, so that you don't need to write any special code to ignore them.

Comments

If you have comments, questions or suggestions about any of the Resolver One documentation, please post them to the Documentation Suggestions Forum.