Entering values into cells
Office Plus supports various methods of entering or pasting values into cells. The behavior of the methods depends on whether the source cell values to be entered include write back formulas, or blank cells, and whether the target cells contain write back formulas, or blank cells.
This table gives an overview of the methods of entering values into cells:
| User Enters or Pastes | Copied What | Target Cell Contains | Result |
|---|---|---|---|
| , Drag & Drop, , , VBA | Number | Formula (number) | Write back. Formula remains. |
| Number | Formula (empty) | Write back. Formula remains. | |
| Empty cell | Formula (number) | Formula deleted. Empty cell. | |
| Empty cell | Formula (empty) | Formula deleted. Empty cell. | |
| Ctrl + M | Number | Formula (number) | Write back. Formula remains. |
| Number | Formula (empty) | Write back. Formula remains. | |
| Empty cell | Formula (number) | Write back empty. Formula remains. | |
| Empty cell | Formula (empty) | Write back empty. Formula remains. | |
| Ctrl + Shift + M | Number | Formula (number) | Formula deleted. Number in the cell. |
| Number | Formula (empty) | Formula deleted. Number in the cell. | |
| Empty cell | Formula (number) | Formula deleted. Empty cell. | |
| Empty cell | Formula (empty) | Formula deleted. Empty cell. | |
| Ctrl + Alt + M | Number | Formula (number) | Write back. Formula remains. |
| Number | Formula (empty) | Write back. Formula remains. | |
| Empty cell | Formula (number) | Formula deleted. Empty cell. | |
| Empty cell | Formula (empty) | Formula deleted. Empty cell. |
is useful when, for example, you want to paste and write back values without overwriting the formula or cell format. In contrast to , preserves the cell format.
Scenarios 1, 2, and 3 describe the methods in detail.
In these scenarios, the source cell and the target cell contain different CELL.GETC formulas. The source cell contains this formula:
=CELL.GETC("Best Practices OLAP","Analysis","[Product].[all tires].[suv (4x4)]")=CELL.GETC("Best
Practices OLAP","Analysis","[Product].[all tires].[suv (4x4)]"). The formula returns 12345.
The target cell contains this formula:
=CELL.GETC("Best Practices OLAP","Analysis","[Product].[all tires].[car tires all
season]").The formula returns 54321.
Scenario 1. Source and target cells contain write back formulas
- : Pastes the source formula to the target cell. No value is written back. That is,
the target cell now contains the same write back formula as the source cell.
Note: The same behavior applies to values that you enter with , Drag & Drop, , or VBA.
- : Writes back the result of the source formula. The target cell and the source cell still contain different formulas, but the value returned by both formulas is 12345.
- : Writes back the result of the source formula. The target cell and the source cell still contain different formulas, but the value returned by both formulas is 12345.
- : Writes back the result of the source formula as a value (12345). The target cell no longer contains a formula.
Scenario 2. Source cell contains a write back formula and target cell contains a value
- : Overwrites the value with the source formula. The cell now contains a formula, not
a value.
Note: The same behavior applies to values that you enter with , Drag & Drop, , or VBA.
- : Pastes the result of the source formula (12345) as a value. The cell still does not contain a formula.
- : Pastes the result of the source formula (12345) as a value. The cell still does not contain a formula.
- : Pastes the result of the source formula (12345) as a value. The cell still does not contain a formula..
Scenario 3. Source cells contain one or more blank cells
- : Pastes the source values and formulas, but does not paste the blank cells.
Note: This behavior is the same as the Paste Special function in Microsoft Excel with the Skip blanks option enabled.
- : Pastes the source values, formulas, and blank cells.