Ask Your Question

Calc: how to properly reference cells from other sheet? [closed]

asked 2013-02-06 20:28:37 +0200

tomek gravatar image

Hello everyone,

I came across the following difficulty:

I copy and "paste special" row from one sheet into some other sheet in the same document. In "paste special" I select the "link" option to reference cells from the original sheet instead of coping values. Cells which have values in the original sheet at the moment when I copy them are referenced properly, however those that are empty are not referenced.

Do you possibly know how to make all cells to be referenced, regardless of whether they have some value or not?

Thank you in advance for help :)

Best Regards

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-26 15:55:10.074030


@tomek it looks like @razon_22's answer is the best answer, it worked for me, if it worked for you, you should upvote and then accept his answer

malachi gravatar imagemalachi ( 2014-07-31 00:38:07 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2013-03-10 08:26:51 +0200

razon_22 gravatar image

updated 2013-03-10 08:27:30 +0200


I think you're describing the proper functioning of the paste special - link command. All you need to do is go to a blank sheet, click in A4 for example, in the formula bar type the equal sign, then click on the tab of the sheet that has the data. Click A3 assuming the data starts in cell A3. Press Enter. Now go back to the new sheet, click on cell A4, move the mouse to the bottom right of the cell and position it over the handle and click and hold down the mouse button and drag the mouse to the right. Release the button when cell J4 is selected. The formula will be copied from cell A4 to J4 and will link to all cells from A3 to J3 on the data sheet even if they don't have a value in them yet. Let us know if this works for you. Thanks.

edit flag offensive delete link more


Confirmed -- it's possible to set the value of a cell (e.g. Sheet2.A4) using a reference to a blank cell in another sheet (e.g. =Sheet1.A3).

Note: For me in LO, if the source cell is empty, the value displayed in the output cell is zero ("0"). I believe that there may be a configuration option to make this display as the empty string instead.

qubit gravatar imagequbit ( 2013-03-10 09:04:12 +0200 )edit

Yes there is, use this formula in sheet 2 if you want the links to show an empty cell if the source cell is empty. =IF(Sheet1.A3="", "", Sheet1.A3)

razon_22 gravatar imagerazon_22 ( 2013-03-10 09:23:03 +0200 )edit

@rmfaile -- I just found the option:

Tools - Options - LibreOffice Calc - View - (uncheck) Zero Values

This is only of limited use, as it turns all zeros into the empty string. So if the source cell really contains "0", that would not be displayed. It would be nice if the output of "= a cell that's blank" was also a blank cell.

qubit gravatar imagequbit ( 2013-03-10 09:35:59 +0200 )edit

answered 2013-03-10 04:43:35 +0200

ROSt52 gravatar image

updated 2013-03-10 09:16:09 +0200

@tomek - in your question you are talking about copying from sheet to sheet. Are the original sheet and the target sheet in the same Calc-file or are they in different Calc files?

edit flag offensive delete link more



This should be a comment on the Original Post as this is not an answer to the Question

malachi gravatar imagemalachi ( 2014-07-31 00:33:45 +0200 )edit

Question Tools

1 follower


Asked: 2013-02-06 20:28:37 +0200

Seen: 52,492 times

Last updated: Mar 10 '13