Cell State Dialog

The Cell State dialog allows you to enable or disable the execution of cells either explicitly or conditionally, based upon the value of a reference cell. Disabled cells will not be executed when the spreadsheet updates, so these cells retain their current values indefinitely, unless they are re-enabled.

Note: As a visual clue, the text of disabled cells will be dimmed or grey.

Configuring the State of One or More Cells

  1. Select a cell or a range of cells in the spreadsheet.

  2. Open the Cell State Dialog:

    From either the Edit or Right-Click menu, select Cell State or click the Edit Cell State link to the right of the formula bar.

  3. Choose one of the following states for the selected cell(s):

    1. Disabled: Selected cell(s) will not execute upon trigger.
    2. Enabled: Selected cell(s) will execute upon trigger.
    3. Conditionally Enabled: Selected cell(s) will be Enabled if the value of a reference cell is non-zero.

  4. If you chose Conditionally Enabled in the previous step:

  5. Choose a cell whose value will determine the state of the selected cell(s) from Step (1).

  6. Whenever Cell Reference evaluates to 0, the selected cell(s) will be Disabled; otherwise, they will be Enabled.

    Note: If a cell is referenced to itself, that reference is ignored.
  7. Click OK.
Note:

An active cell's state is visually identifiable in the toolbar. For example: D3 is conditionally enabled and when selected, the absolute reference (if $A$3) is shown in the Edit Cell State link to the right of the formula bar.

Copying and Pasting Cell States

If you copy and paste a cell whose state is Conditionally Enabled, the state of the pasted cell will depend on the type of Cell References used in the copied cell's Conditional Reference. Cell Reference will behave as either a relative or an absolute cell reference when the cell is copied and pasted to a new location. Cut and paste, however, will always leave Cell Reference identical to the cell that was cut.

The difference between relative and absolute conditional cell states arises when copying and pasting cell contents. If you copy a cell whose state is Conditional - Relative and paste it to a new location, the state of the pasted cell will be dependent upon the value of a cell relative to the location of Cell Reference for the copied cell.

On the other hand, copying a cell whose state is Conditional - Absolute and pasting it to a new location will always result in a pasted cell whose state is absolutely dependent upon the evaluation of Cell Reference for the copied cell, no matter where it is pasted.

Note: Using Cut and Paste (instead of Copy and Paste) simply moves the cell contents; no translation of relative cell references is performed. The pasted Cell Reference will be identical to the cell that was cut.