Ask Your Question

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

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

Otto903 gravatar image

updated 2020-07-22 08:13:50 +0100

Alex Kemp 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 reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-07-22 08:14:06.276995

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

Question Tools

1 follower


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

Seen: 950 times

Last updated: Jan 21 '16