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.
Configuring the State of One or More Cells
- Select a cell or a range of cells in the spreadsheet.
-
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.
-
Choose one of the following states for the selected cell(s):
- Disabled: Selected cell(s) will not execute upon trigger.
- Enabled: Selected cell(s) will execute upon trigger.
- Conditionally Enabled: Selected cell(s) will be Enabled if the value of a reference cell is non-zero.
-
If you chose Conditionally Enabled in the previous step:
- Choose either Relative or Absolute for the type of Cell References.
- Click Select Cell to enter into Cell Selection Mode.
- Choose a cell whose value will determine the state of the selected cell(s) from Step (1).
-
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. - Click OK.
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.