Faq/Calc/145

    From The Document Foundation Wiki
    < Faq‎ | Calc


    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