Syntax #1:
SET_VALUE(cell_reference, formula)
Stores the result value of the formula as a value in the given cell (which must not contain a formula).
Example:
SET_VALUE([fred]A1, 56)
sets the value of cell A1
in the worksheet fred
to 56
.
Note that SET_VALUE
is the only spreadsheet function which overwrites other cells.
You need to use it carefully because any data you type into the target cells will be overwritten the next time SET_VALUE
recalculates.
Syntax #2:
Enables you to store the contents of the array across the given range of cells.
Example:
SET_VALUE(F1:F10, E1:E10*VAT)
multiplies each of the cells from E1
to E10
by the current value of the name VAT
and places the result in the cells F1
to F10
.
Using arrays and SET_VALUE
in this way can remove large numbers of formulae from your worksheets in this way and save memory;
cells F1:F10
could each have contained a formula E1*VAT
, E2*VAT
, and so on.
You can also combine SET_VALUE
with functions which return an array, to write the result of the function into a range of cells. For example:
SET_VALUE(A10:E15, TRANSPOSE(A1:E5)
writes the transposed version of the range A1:E5
into the cells A10:E15
. Also,
SET_VALUE(C1:C10, SORT(A1:A10))
sorts the values in A1:A10
and puts the sorted array in /C1:C10
.
Syntax #3:
SET_VALUE(array, value, x:Number, y:Number)
With the addition of the two optional parameters x and y SET_VALUE
is able to write into a specified position in arrays and ranges.
Example:
SET_VALUE(A1, 27, 2, 3)
will replace the entry specified by the second column and third row in the array contained in A1
with the value 27
.