Frequently asked questions - Calc

From The Document Foundation Wiki
< Faq‎ | Calc
Jump to: navigation, search

How to update references to linked cells when copying form controls

Form controls linked to cells were created on a sheet. When copying (sheet or controls) to another sheet, these references are not updated and continue to target the original linked cell.

The following procedure[1] uses an example spreadsheet containing four sheets (A, B , C, D). The B sheet includes a Text box linked to A1 and a Counter linked to A2. You want to copy these controls into sheet D.

screenshot
Controls in sheet B

The principle is to paste controls into an intermediate spreadsheet in which the positions of the sheets are swapped.

Please follow all steps :

  1. Copy the range containing the controls
  2. Create a new "work spreadsheet" containing four sheets with same names in the "initial" but with sheets "source" (B) and "target" (D) swapped:
    copie d'écran
    "Work" spreadsheet : sheets B and D swapped
  3. Paste in the sheet D of the "work" spreadsheet
  4. Save (you can use any name) and close the "work" spreadsheet
  5. Reopen the the "work" spreadsheet
  6. Restore the initial positions of sheets:
    copie d'écran
    "Work" spreadsheet : sheets positions restored
  7. Copy the range containing the controls
  8. Paste into the sheet D of the original spreadsheet
  9. Save, close, and reopen the spreadsheet.

Notes:

  1. For a macro solution see this link