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

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

tomek
21 ●1 ●1 ●3

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

Comments

@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 ( 2014-07-31 00:38:07 +0200 )edit

add a comment

1

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

razon_22
1904 ●2 ●23 ●27

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

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

edit flag offensive delete link

Comments

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.

qubit ( 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 ( 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 ( 2013-03-10 09:35:59 +0200 )edit

add a comment

0

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

ROSt52

14145 ●106 ●155 ●185

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

Comments

2

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

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

add a comment

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

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

Comments

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

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

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

Comments

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

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

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