Calc: how to properly reference cells from other sheet?

English
,
#1

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 :slight_smile:

Best Regards

Replicate data in cells on other sheets within same document on a global document basis
#2

@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

#5

@tomek

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.

#6

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 4.0.0.3, 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.

#7

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)

#8

@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.

#3

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

#4

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

#9

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