Move a column from one sheet to another and keep the same data

English
, ,
#1

I have a sheet with a reference to another sheet in column column A:

=Sheet2.A12

When I move this column to another sheet and copy it to column B, the reference to the data changes:

=Sheet2.B12

I need it to reference the original data source, =Sheet2.A12

I have been getting this to work by manually going into each cell and changing the formula to reflect the original data source, =Sheet2.A12. I have about 30 items in a vertical column and changing each cell is time consuming.

Can I copy and paste a vertical column from one to another column and keep the formula reference the same?

#2

Please review the documentation.

Relative and absolute references, page 13.

#3

Or more direct link, same content as in the online help, Addresses and References, Absolute and Relative - LibreOffice Help

#4

I replayed your scenario or a similar one.

  1. Start a new .ods file and add one more sheet. (Sheet1, Sheet2)
  2. Write 1 in Sheet1.A1 and drag down with the small black square to fill 20 cells (Sheet1.A1:Sheet1.A20) with numbers 1–20.
  3. Write a formula in Sheet2.A1, =2*Sheet1.A1, and drag to fill cells Sheet2.A1:Sheet2.A20.
  4. Add a new column left of Sheet2.A by clicking the header of Sheet2.A and choosing ‘Insert column left’ from the context menu.
  5. Cut column Sheet1.A by clicking the column header and typing ctrl-x.
  6. Paste the data back by clicking on the header of Sheet2.A and typing ctrl-v to paste.

With these steps, the references are kept intact.

#5

I replayed your scenario or a similar one.

  1. Start a new .ods file and add one more sheet. (Sheet1, Sheet2)
  2. Write 1 in Sheet1.A1 and drag down with the small black square to fill 20 cells (Sheet1.A1:Sheet1.A20) with numbers 1–20.
  3. Write a formula in Sheet2.A1, =2*Sheet1.A1, and drag to fill cells Sheet2.A1:Sheet2.A20.
  4. Add a new column left of Sheet2.A by clicking the header of Sheet2.A and choosing ‘Insert column left’ from the context menu.
  5. Cut column Sheet1.A by clicking the column header and typing ctrl-x.
  6. Paste the data back by clicking on the header of Sheet2.A and typing ctrl-v to paste.

With these steps, the references are kept intact.

#6

If its the cell reference only you can lock it like this.

sheet2.$B$12
#7

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.