6. Dynamic Cell References

Cell(c,r) creates a dynamic reference to a cell using expressions for the column and row. In contrast, the numeric function cell(c,r) produces the value of a cell. So, for example, if B1 is 9, cell("b",1) produces 9 whereas Cell("b",1) produces B1 as though you had typed B1 there. For both Cell and cell, the column is specified as a numeric expression, or as a literal string, or as a cell or symbol containing a string.

Cell can be used anywhere that cells are used, including range specifications, like m = max(Cell(1+a0,2*b1):c4); and Cell(1,0):Cell(N,0) = { expr_list... };

Except when used in the Copy command, Cell is evaluated once when it occurs, so it produces a static cell reference, but does so dynamically using row and column values which may be based on results computed in the spreadsheet. In the Copy command, Cell is evaluated separately for each destination cell, so any formulas in Cell are relative to the destination cell.

Example using Cell, Copy, copy, etc. with formulas and strings:

% cat Cell.ss

b1:b3 = { 0, 1, 2};  b0:g0 = { 4, 8, 12, "abc", "efg", "xyz" };
//
// b1:b3 will be used as column indices into b0:g0
//
//                        // equivalents:
Copy a2:a4 Cell(1+b1,0);  //  copy a2:a4 b0:d0;
copy a6:a8 Cell(1+b1,0);  //  copy a6:a8 b0;
b6:b8 = { Cell(1+b1,0) }; //  b6:b8 = { b0 };
c6:c8 = { cell(1+b1,0) }; //  c6:c8 = { b0, c0, d0 };
//
Copy c2:c4 Cell(4+b1,0);  //  copy c2:c4 e0:g0;
copy d2:d4 Cell(4+b1,0);  //  copy d2:d4 e0;
e2:e4 = { Cell(4+b1,0) }; //  e2:e4 = { e0 };
f2:f4 = { cell(4+b1,0) }; //  f2:f4 = { e0, f0, g0 };
//
eval; format "%g"; reset a9; print all;

% SS Cell.ss

	A	B	C	D	E	F	G
0		4	8	12	"abc"	"efg"	"xyz"
1		0
2	4	1	"abc"	"abc"	E0	cell((4+B1),0)
3	8	2	"efg"	"abc"	E1	cell((4+B2),0)
4	12		"xyz"	"abc"	E2	cell((4+B3),0)
5
6	4	B0	cell((1+B1),0)
7	4	B1	cell((1+B2),0)
8	4	B2	cell((1+B3),0)
9
	A	B	C	D	E	F	G
0		4	8	12	abc	efg	xyz
1		0
2	4	1	abc	abc	0	0
3	8	2	efg	abc	0	0
4	12		xyz	abc	0	0
5
6	4	4	4
7	4	0	8
8	4	1	12
9

An example application assigning student letter grades:

% cat Copy.ss

b0:b3 = { 71, 92, 66, 83 };
a5:a8 = { "A", "B", "C", "F" };
Copy a0:a3 Cell( "a", 4 + (b0 >= 90 ? 1 : b0 >= 80 ? 2 : b0 >= 70 ? 3 : 4));
print;

% SS Copy.ss

	A	B
0	C	71.00
1	A	92.00
2	F	66.00
3	B	83.00
4
5	A
6	B
7	C
8	F