SET_VALUE

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:

SET_VALUE( range, array )

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( range or 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 ranges and arrays.

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.