[Tutorial] Formula Adjustments during Copy and Move

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Locked
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

[Tutorial] Formula Adjustments during Copy and Move

Post by MrProgrammer »

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.

For convenience in writing, this tutorial will just use the term "row" even though the situation described will apply equally to rows and columns. Likewise, "above" denotes: above a row or to the left of a column; "below" denotes: below a row or to the right of a column.

For formulas which use absolute references in formulas ($ before the row number) the meaning is: the row number specified. However, for formulas which use relative references to a row (no $) the meaning is: the row at the implied offset from the formula's cell. So if a relative-reference formula in D5 refers to a cell in "row 7", the meaning in not row 7 but "the row two after the cell with the formula" (because 2 is 7 minus 5) . The distinction is important when formulas are copied. Additional information about absolute and relative references can be found in section 8. Using formulas and cell references of Ten concepts that every Calc user should know.

Definitions:
• A range: A rectangular set of one or more cells; ranges are written cell:cell; a single cell range could be written simply as cell.
• A formula's precedents: The set of cells which are used in the formula's evaluation
• A formula's dependents: The set of cells which use a formula's cell in their evaluation
• A formula's appearance: What you see in the Formula Bar when the cell is selected
• A formula's meaning: See the paragraph above
Copy: The Copy/Paste operation; on some platforms, Ctrl+Drag will also copy cells
Move: The Cut/Paste Operation; Drag will also move cells

The basic concepts for a single formula are:
When we copy a formula, Calc copies its meaning.
• If a copied formula uses only absolute references, its appearance and value do not change.
• If a copied formula uses any relative references, the appearance of the formula will change and the value of the formula can change.
• Calc does not change dependents which use a copied formula's cell, since that value still exists in the cell which was copied.
When we move a formula, Calc preserves the formula's value and appearance, and values in dependent cells.
• If a moved formula uses only absolute references, its meaning does not change.
• If a moved formula uses any relative references, its meaning is changed so it will access the same precedents as the original formula.
• Dependents in sheets which specify a moved cell are changed to reference the new cell even if the reference to the moved cell is absolute and even if the dependent's cell is protected.
• Dependents in defined names using absolute references to a moved cell ($sheet.$column$row) are changed to reference the new cell.
• Dependents in defined names using relative references to a moved cell (sheet or column or row) are not changed to reference the new cell.
• Dependents in conditional formatting using absolute references to a moved cell ($sheet.$column$row) are changed to reference the new cell.
• Dependents in conditional formatting using relative references to a moved cell (sheet or column or row) are not changed to reference the new cell.
• If you perform Cut, Paste, Paste, Paste, … the first Paste is a Move and the others are treated as Copy operations.

If we use Edit → Fill → Down/Right/Up/Left, this is a Copy operation. The meaning of the cell being copied is preserved. This operation is useful for placing the same formula in all the cells of a column.

Calc allows ranges to be copied or moved in one operation. The basic concepts above apply but additionally:
• Dependents with ranges which include cells outside the copied/moved range are not adjusted when the block is moved.
• The operation may copy, move, or replace formulas, precedents, and dependents.

To understand what happens when blocks are copied or moved, it can help to separately consider what is happening
• to the formula,
• to its precedents, and
• to its dependents.
Interactions between them can cause the result to differ from the basics above. For example, a block copy can overlay the dependent of a copied formula, thus changing it. Or if we move a block which contains both a formula and a precedent, the formula's appearance changes. There are many combinations of combined move or copy operations on formulas, precedents, and dependents. Listing them all would be impractical, and would probably create confusion instead of furthering understanding. However, some examples are provided later.

One can duplicate a formula by typing it into two separate cells. Or, if a cell contains a formula, duplicate it by copying the text of the formula from the Formula Bar and pasting it in another cell. To duplicate a range of cells, select them, Edit → Cut, select the new location, Edit → Paste, select the original location, Edit → Paste. Note the order of operations is Cut/Paste/Paste and not Copy/Paste/Paste nor Cut/Paste/Copy/Paste. The original and new locations will now contain the same values and formulas (by appearance). Dependents will reference the new locations. If dependents need to reference the original locations, perform Cut/Paste/Paste a second time, with the Cut at the new location, the first paste at the original location, and the second paste at the new location.

When we use Insert → Cells, this is similar to moving the rows beneath them downward. Edit → Delete Cells is similar to moving the rows beneath upwards. The values, precedents, and appearance of the cells being moved are preserved. However, for dependents:
• References to deleted cells are changed to error values (#REF!).
• Absolute references in defined names to deleted cells are changed to error values (#REF!).
• Relative references in defined names are relative to the active cell. Because they don't reference specific cells, they are not changed.
• If a delete is located before or at the top of the range, both the beginning and end of the range are changed.
• If a delete is located within or at the bottom of the range, only the end of the range is changed.
• If a delete is located after the range, the range is not changed.
When the Expand References option is not set
• If an insert is located before or at the top of the range, both the beginning and end of the range are changed.
• If an insert is located within the range, only the end of the range is changed.
• If an insert/delete is located at the bottom of or after the range, the range is not changed.
When the Expand References option is set
• If an insert is located before the range, both the beginning and end of the range are changed.
• If an insert is located at the top of, or within, or at the bottom of the range, only the end of the range is changed.
• If an insert is located after the range, the range is not changed.
The Expand References option is OpenOffice Calc → General → Expand references when new rows/columns are inserted. Options are set with OpenOffice → Preferences on a Mac, Tools → Options on other platforms. This option is treated as if it were off when the range is only one row high. Note that when the option is off, insert and delete perform similar processing.

If you rearrange rows using Data → Sort, the values in dependent cells and defined names are not preserved. So don't sort cells which are referenced by other cells/names unless you use Option → Copy sort results to → {other cells}. Also, formulas which are relocated are adjusted as if they were copied, not moved (see Basic concepts section above). This is not what many people expect, to it's best not to attempt to sort ranges which contain formulas, as the result can be difficult to understand. These topics discuss the details.
Sort does not work in sheets with vertical cell-references
[Solved] Sorting on the results of formulas
Formulas Don't Adapt to Sorting Their Reference Cells
Copy Move.ods
(8.57 KiB) Downloaded 648 times
Open the spreadsheet in the attachment to see examples of how copy and move operations affect cells. Each example is independent and expects the initial spreadsheet to be loaded. To try multiple examples, either use Edit → Undo after each, or use File → Reload. This spreadsheet uses the Expand References option.
  • --- Copy a cell examples ---
  • Cell E5 is =SUM($B$5:$D$5) which means "sum B5 through D5" (using absolute references). If you copy it to E6 the formula will remain =SUM($B$5:$D$5). The meaning, appearance, precedents, value, and dependent (F5) did not change.
  • Cell E2 is =SUM(B2:D2) which means "sum the three cells to the left" (of E2, using relative references). If you copy it to E3 the formula will be =SUM(B3:D3). The meaning "sum the three cells to the left" and dependent (F2) did not change. The appearance, precedents, and value changed.
  • --- Move a cell examples ---
  • Cell E5 is =SUM($B$5:$D$5) which means "sum the B5 through D5" (using absolute references). If you move it to E6 the formula will remain =SUM($B$5:$D$5). The meaning, appearance, precedents, and value did not change. Dependent cell F5 was changed from =2*E5 to =2*E6 and the dependent's value remains unchanged but its appearance and meaning changes.
  • Cell E2 is =SUM(B2:D2) which means "sum the three cells to the left" (of E2, using relative references). If you move it to E3 the formula will remain =SUM(B2:D2). The moved formula's meaning and dependent (F2) changed. The appearance, precedents, and value of the moved formula did not change.
  • --- Fill example ---
  • If we fill E2 down to E4, this is like copying E2 to E3 and E2 to E4. The meaning is copied but the appearance and values change.
  • --- Copy a range examples ---
  • If we select B5 through F5 and copy them together to B6 through F6, the formula in E6 has the same meaning, appearance, precedents, and value as E5. F5 (dependent of E5) did not change. F6 becomes a dependent of E6.
  • If we select B2 through F2 and copy them together to B6 through F6, the formula in E6 has the same meaning and value as E2. The appearance and precedents changed. F2 (dependent of E2) did not change. F6 becomes a dependent of E6.
  • --- Move a range examples ---
  • If we select B5 through F5 and move them together to B6 through F6, the formula in E6 has the same value as E5 did. The meaning, appearance, and precedents changed. F5 (dependent of E5) is gone. F6 becomes a dependent of E6.
  • If we select B2 through F2 and move them together to B6 through F6, the formula in E6 has the same meaning and value as E2. The appearance and precedents changed. F2 (dependent of E2) is gone. F6 becomes a dependent of E6.
  • --- Insertion examples with option Expand References ---
  • If we select column A and use Insert → Columns, all of the SUM formulas (now in column F) have the beginning and end of the range adjusted.
  • If we select column B and use Insert → Columns, F2 and F5 will now sum four columns. The beginning of their range (column B) was not changed. The end of the range was changed (D → E). F4, which uses a relative defined name, still sums three columns.
  • If we select column E and use Insert → Columns, F2 and F5 will now sum four columns. The beginning of the range was not changed. The end of the range was changed. F4, which uses a relative defined name, still sums three columns.
  • If we select column F and use Insert → Columns, all of the sums in column E are unchanged.
  • --- Insertion examples without option Expand References ---
  • If we clear option Expand References, select column B and use Insert → Columns, all of the sums in column F have the beginning and end of the range adjusted.
  • If we clear option Expand References, select column E and use Insert → Columns, all of the sums in column F are unchanged.
Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section. In addition, discussion about how Calc handles copy/move/insert/delete operations should take place in the Applications → Calc forum where more people will see it and participate. If that discussion creates improvements for this tutorial, I will update it. Keeping the discussion separate will make it easier for readers of the tutorial since they will not need to wade through interim dialog.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Locked