Ask Your Question
0

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:

=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?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

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

m.a.riosv gravatar image

Please review the documentation.

Relative and absolute references, page 13.

https://wiki.documentfoundation.org/i...

edit flag offensive delete link more

Comments

Or more direct link, same content as in the online help, https://help.libreoffice.org/Calc/Add...

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

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
0

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

Chris UK gravatar image

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

sheet2.$B$12
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 548 times

Last updated: Jan 21 '16