Ask Your Question

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

asked 2016-01-19 20:47:40 +0100

Otto903 gravatar image

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


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


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?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2016-01-21 01:31:37 +0100

m.a.riosv gravatar image

Please review the documentation.

Relative and absolute references, page 13.

edit flag offensive delete link more


Or more direct link, same content as in the online help,

erAck gravatar imageerAck ( 2016-01-22 17:23:27 +0100 )edit

answered 2016-01-21 08:18:00 +0100

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.

edit flag offensive delete link more

answered 2016-01-24 00:33:54 +0100

Chris UK gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-01-19 20:47:40 +0100

Seen: 490 times

Last updated: Jan 21 '16