basics : cell references and formulas : referring to cells in other rows : independent variables : cell formatting : cell styling : cell display : scripts and HTML : conclusion (objectsheet home)
Next, we look at how to refer to other cells in a formula. Start with a new sheet again:
(We're done with the sheet from the previous section; you can just hit the close box to make it go away.)
Put a few values in column a;
they can be simple numbers or text, or general formulas:
We refer to these values from column b just by using the
name of the column, or 'a',
in a formula:
In the objectsheet, to refer to other cells within
a row, simply use the column name. In the above sheet, the
formula in row 0 column b is "=a*2+1".
In column 0, a=3, so b evaluates to 3*2+1, or 7. As with a
traditional spreadsheet, you can change any cell and when you
exit the cell, all formulas and values are instantly updated to
reflect your change.
Referring to a cells in other rows is taken up in the next section.
Let's extend this thought by adding a couple more columns and doing something a little more coherent. Start a fresh sheet:
The plus sign image at the top center of the sheet is the expandor. It adds rows ('objects') and columns ('properties') to the sheet. You use it by placing your mouse over that little plus sign and dragging. Dragging to the right adds columns (properties) and dragging downwards adds rows (objects). As you drag, the number of objects and properties to be added are indicated in the status line (at the bottom).
Let's drag a little to the right so that we add two columns. You should end up with something like this:
Note: in this current proof of concept application, you can only extend an objectsheet at its ends; you cannot insert rows or columns elsewhere, nor can you delete them. If you add too many objects or properties, you can leave them as is (they won't hurt anything), or you can just close that window and start again.
Let's enter a few specific numbers and formulas:
So the value in column b is the value of column a times 24; column c is column b times 60 and column d has a similar relationship to column c. We can add data in other rows (see below) and type in the same formulas. So long as the formula references the same row, we need only use the column name (a, b, c, etc.).
Let's make these references more meaningful than 'a', 'b', etc. Unlike spreadsheets, you can rename the columns in an objectsheet. Let's edit the cells in the "name" row and type new column names. You only need to change the yellow-background 'name' cells. All other references to that name in the sheet are automatically updated:
The sheet just became a lot more meaningful. The relationships between the columns and the formulas make much more sense than a spreadsheet.
But we're not done. There's one more giant step that will make our objectsheet even simpler and clearer.
Start with a new sheet:
Add two columns as above:
Now, we'll use the formula template row. With this, well enter our formulas once for each column and they'll instantly apply to that whole column.
Show the formula row by selecting that entry in the "VIEW" menu like so:
The formula row appears below the name row:
Now, we simply enter the same formulas as above, but into the formula row instead of the cells in the "body". The "=" prefix is optional here. The result is:
Nothing too exciting just yet. All the values are zero since all the "a" values are blank. We "breathe life" into the sheet by entering our starting numbers into column a, as above:
Viola! All the values are instantly updated. All we had to do
to get here was to enter values into 5 cells-- no copy and paste
of formulas, no need to type in cryptic C3*B3 spreadsheet
formulas.
Let's finish this by again renaming the columns. Again, we just change the column names; the formulas update automatically:
The logic of this objectsheet is defined by two rows of names and formulas. It is easy to understand at a glance. Other "rules" that apply to formatting, constraint checking, etc., occupy other rows (currently hidden) in the Template. Much or all of the objectsheet's logic can be placed in this area, making it easy to understand even complex models.
If we wanted, we could hide the formula row by selecting it again in the VIEW menu. But leaving formulas visible makes the logical flow of the sheet more clear. Unlike the traditional spreadsheet, we can see both the results and the formulas and other rules that got us there, in one view. No hunting around uncovering one formula at a time, as one does to "figure out" a spreadsheet.
Clarity is further aided by smart cell shading. Columns that have an entry in the formula row are indicated by light gray shading. Columns that expect manual entry are left white. This serves as a cue to where you are expected to enter data manually.
You can still change ("override") the contents of a
automatically calculated cell. Gray-shaded cells are still
editable. Below, we have done just that, typing the number 24 in
row 2, column 'hr':
Here, the sheet calculates the value based on what was entered directly in the cell, ignoring the template formula for that cell. Also, the cell background reverts to white, making the override conspicuous. This is an important factor in reducing the chance of error in spreadsheet usage. While you can provide traditional spreadsheets with such behavior, doing so is manual and cumbersome. With objectsheets, it is automatic.
To remove the override and revert back to the automatic
calculation, just erase the cell contents (again, row 2, column 'hr'):
Note: we will use this sheet in the next section, so don't close it just yet.
We have covered here same-row references in objectsheets; because so many links between cells are across the same row, it makes sense for these references to be simple names. Next, we consider more general referencing around the objectsheet.
next: referring to cells in other rows
rk-11 apr 01