[Solved] Copy cell value from a spreadsheet into another

[Ulin]

I am struggling to find a way to display the results of a formula when editing into a different document. It picks up the formatting, but not the results.

Can anyone help please

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[erbsenzahl]

to display the results of a formula when editing into a different document. It picks up the formatting, but not the results.

Hi,
you write in forum Install, Setup and Troubleshooting...?
Probably you mean a Calc formula displayed in Writer? If so, so check possibilities of DDE or OLE.
Please give more details for a more detailled answer. - Cheers

EDIT
Could you alter the topic/description of your problem? Multiple Documents is too unspecific, IMHO.

[Ulin]

Sorry, I was writing under Troubleshooting but you are probably correct. What do you mean by DDE or OLE?

My problem is that I have a document full of information. I want to pick up some of that information in another document. It picks up the format and text but not the numbers where a formula is giving a result. It just returns a zero.

[FJCC]

Please give much more detail. By "document", do you mean a spreadsheet? What are you doing to get the information from one document to the other?

[robleyd]

I want to pick up some of that information

By this, you mean adding a link to data in another document? What type of document is the source, and the target? What exactly have you tried so far?

[Ulin]

They are both OpenOffice spreadsheets. I want to pick up cell AI61 (cell AI61 is a formula totaling cells C61 to AG61) from one document into the other document but it only returns the value zero in the destination cell. If I try to pick up cell AI3 which contains the word TOTAL, it works. If I type TOTAL into cell AI61 it still only returns the value zero in the destination cell.

[robleyd]

As asked, what exactly have you tried so far? Please remember that only you can see what you are doing - we have to rely on you telling us in detail what you have done and what results you get.

[Bill]

I want to pick up...

What does "pick up" mean? Drag and drop? Copy and paste? Something else? Drag and drop will copy the content of the cell. If the content is a formula, then the formula is copied, not the result of the formula.

[Ulin]

In the destination cell I put '=' and then highlight the cell in the other spreadsheet and press enter. I don't know what you call this method. Whichever method it is, it doesn't display the formula in the destination cell, only the value '0'.

[robleyd]

You have created a link to an external file which, correctly done, should show in the target spreadsheet, the value produced by the formula in the source cell.

If you want to display the actual formula in a remote cell, whether in the same or a different spreadsheet, you can use the FORMULA() function; see more information on the HELP - F1.

If this isn't what you are trying to do, please describe what you are trying to achieve.

[Ulin]

I want to display the result of the formula in both spreadsheets. However, in the destination spreadsheet I can only display the value zero.

[Hagar Delest]

I made a quick test with LibreOffice and it works fine. I put "=" in a cell and then select a cell containing a formula in another spreadsheet, it displays the value (hence the result of a calculation). Same if I close the source spreadsheet, even after closing all spreadsheets and opening the one that points to the other (LO asking if the values have to be updated).

[Ulin]

I don't know what LibreOffice is, but it sounds as though you have done exactly the same as me in OpenOffice. However, in OpenOffice it doesn't work.

[Hagar Delest]

It is a rather basic operation, thus AOO should be able to do it without problems.
However, we can never say now with AOO. Try LibreOffice (there are portable versions to have a first quick look), it forked from the same base (OpenOffice.org), thus it is quite the same. But it is much more actively developed (it drained quite the whole lot of developers).

[LastUnicorn]

I don't know what LibreOffice is...

For some quick information on LibreOffice and good reasons why you should switch from OpenOffice to LibreOffice see this thread: [Tutorial] Considering a Switch from OpenOffice to LibreOffice? Some Useful Information

You can also search the internet for more information on LibreOffice.

[RoryOF]

Attached are two sample spreadsheets, Fred1.ods and Fred2.ods

Cell D1 in Fred1.ods has a simple formula calculating from A1, B1, C1 in that sheet. Fred2.ods links its cell A1 to Fred1.ods cell D1. All works well.

If Ulin is getting 0 as an answer in his destination sheet, perhaps his cell format is incorrect and might be showing a numeric value for a text link.

[Ulin]

Thanks for your help. My formats are the same as yours and my linking of spreadsheets are the same as yours, but mine does not work.

[RoryOF]

Verify your cell format in the destination sheet.

[Ulin]

I have done and it is the same as yours.

[RoryOF]

Did your spreadsheet(s) originate in Excel, or were stored at some time in Excel format (.xls or .xlsx)?

Can you make two simple test spreadsheets, similar to my examples, and successfully link them?

[Ulin]

I have no link to XLS. The only difference I can see between mine and yours is that your sheet1 is not in apostrophes, but mine is. If I try and put yours in apostrophes it automatically removes them and if I try to remove mine it automatically puts them back.

[Hagar Delest]

Can you share a set of 2 files as done by Rory that demonstrate the issue?
No need of the whole files that may contain sensitive data.

[Bill]

I want to display the result of the formula in both spreadsheets. However, in the destination spreadsheet I can only display the value zero.

Did you perhaps add the cell in the source file, then create the link in the destination file before saving the source file? If so, then saving the source file and reloading the destination file should display the correct value in the linked cell.

[Ulin]

How do I attach the two Spreadsheets on here?

[LastUnicorn]

To post an attachment to the forum do the following:

1. Look at the text-box (aka message-box) into which you would normally enter your 'Reply' text.
2. Below the text-box you will see a button titled Full Editor & Preview. Click on that button.
3. A new text-box will appear with a lot of useful buttons on top for replies. There is a button there for adding a URL to your reply, should you need to do so. Hover over the buttons to see what functionality they provide.
4. Look below that text-box and you will see two tabs: Options and Attachments. Click on the Attachments tab.
5. Now you can add your attachment to your reply. Maximum file size 128 KiB per attachment.
6. If your attachment is too large then use a file sharing site like MediaFire or similar. Upload your file to the sharing site then make a link in your reply to the file on the sharing site.

P.S. Do not include sensitive or highly personal information in your attachment.

[RoryOF]

Use PostReply or FullEditor windows. Below the submit button you will find Attachments tab, with an Add Files button.

[Bill]

I have no link to XLS. The only difference I can see between mine and yours is that your sheet1 is not in apostrophes, but mine is. If I try and put yours in apostrophes it automatically removes them and if I try to remove mine it automatically puts them back.

Does your sheet name have a space in it? Sheet names with spaces are enclosed in single quotes. That shouldn't be a problem.

[Ulin]

That is exactly what that problem was. I have now renamed with no spaces, but it still doesn't work. It is now 10.15pm where I am so I am giving it a rest for tonight. Thank you all for your help.

[RoryOF]

A general tip: it is best to omit spaces from filenames. If, for legibility they are felt to be desirable, use an underscore instead. It is also good, when naming files, to use only plain ASCII characters - i.e., those with no diacritical markings.

[MrProgrammer]

Hi, and welcome to the forum.

I am struggling to find a way to display the results of a formula when editing into a different document. It picks up the formatting, but not the results.

A formula will access the value of the cell in the other document. A Calc formula cannot access its formatting. Formatting for a cell is set on the sheet which contains the cell.
[Tutorial] Ten concepts that every Calc user should know, 9. Using functions and cell ranges

However, in the destination spreadsheet I can only display the value zero.

I can reproduce that situation. Open a new spreadsheet. Save the empty spreadsheet as A.ods using File → Save As. Type the value 5 in A1 and press Enter. Do not save the spreadsheet. Open a new spreadsheet. Save the empty spreadsheet as B.ods using File → Save As. Type = in A1. Do not press Enter. Use the Window menu to switch to A.ods. Click cell A1 which shows the value 5. Use the Window menu to switch to B.ods. Press Enter. The value 0 will be displayed because the entry 5 in A.ods has not been saved to disk. On disk, cell A1 of A.ods is empty, with value 0. An external file reference can only access data which has been saved to disk.

Solution: Use the Window menu to switch to A.ods. File → Save. Use the Window menu to switch to B.ods. Edit → Links → Update.

I don't know is this is what is causing Ulin's difficulty. I won't contribute further to this topic unless Ulin attaches documents which demonstrate the problem. I will ignore any pictures; the attachments must be spreadsheets because pictures don't contain the information needed to diagnose the problem. Since the difficulty might be caused by failure to save the data, after uploading spreadsheets, Ulin should verify that the problem can be reproduced by those attachments when downloaded from the forum. Attached documents which don't reproduce the problem are of no value unless you can explain, as I did above, how we can get the result you experience.


If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.