Linking spreadsheets

Discuss the spreadsheet application

Linking spreadsheets

Postby bbc0 » Wed Oct 16, 2019 12:50 am

I have 2 spreadsheets AA.ODS and BB.ODS I would like to add the the value in cell A1: AA.ODS to A1: BB.ODS and dipslay the value in A5 in both AA.ODS and BB.ODS and have them linked so that if any of the values are changed in A1 in either .ODS the A5 value in each document will update. I can do this in Excel but can't seem to get it to work in Numbers.
Any help would be appreciated.
OpenOffice 4 on MacOS Mojave
bbc0
 
Posts: 11
Joined: Wed Oct 09, 2019 5:53 am

Re: Linking spreadsheets

Postby FJCC » Wed Oct 16, 2019 2:21 am

The formula to bring a value in from one file to another looks like
Code: Select all   Expand viewCollapse view
='file:///C:/Users/fjcc/Desktop/LastValue.ods'#$Sheet1.A1

The source data is in the file LastValue.ods and the cell is Sheet1.A1.

Does that get you what you need?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7490
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Linking spreadsheets

Postby RusselB » Wed Oct 16, 2019 2:27 am

Welcome to the Forums.
First off, please understand that Calc is not Excel or Numbers. They are similar, but not the same.
Now to answer your question, the simplest method is as follows:
1) Open both AA.ODS and BB.ODS
2) Pick one of the spreadsheets... you can choose which one in the Window menu.
3) Go to cell A5
4) Enter the formula
Code: Select all   Expand viewCollapse view
=AA.ODS#$Sheet1.$A1+BB.ODS#$Sheet1.A1

Please note that if you have renamed the sheets, you'll have to change Sheet1 in above code to the appropriate sheet name.
5) Pick the other spreadsheet using the Window menu and repeat steps 3 and 4.

Note: Calc uses the information from the last saved version of a spreadsheet to generate the results for a linked calculation.
ie: If you change A1 in AA,ODS, the link in A5 will show the updated information, but the link in A5 of BB.ODS will not reflect the updated information until AA.ODS has been saved with the updated information and the links have been updated (possibly using Edit -> Links -> Update)
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Linking spreadsheets

Postby bbc0 » Wed Oct 16, 2019 2:36 am

RusselB wrote:Welcome to the Forums.
First off, please understand that Calc is not Excel or Numbers. They are similar, but not the same.
Now to answer your question, the simplest method is as follows:
1) Open both AA.ODS and BB.ODS
2) Pick one of the spreadsheets... you can choose which one in the Window menu.
3) Go to cell A5
4) Enter the formula
Code: Select all   Expand viewCollapse view
=AA.ODS#$Sheet1.$A1+BB.ODS#$Sheet1.A1

Please note that if you have renamed the sheets, you'll have to change Sheet1 in above code to the appropriate sheet name.
5) Pick the other spreadsheet using the Window menu and repeat steps 3 and 4.

Note: Calc uses the information from the last saved version of a spreadsheet to generate the results for a linked calculation.
ie: If you change A1 in AA,ODS, the link in A5 will show the updated information, but the link in A5 of BB.ODS will not reflect the updated information until AA.ODS has been saved with the updated information and the links have been updated (possibly using Edit -> Links -> Update)


Thankyou I will give this a try.
OpenOffice 4 on MacOS Mojave
bbc0
 
Posts: 11
Joined: Wed Oct 09, 2019 5:53 am

Re: Linking spreadsheets

Postby bbc0 » Wed Oct 16, 2019 2:38 am

FJCC wrote:The formula to bring a value in from one file to another looks like
Code: Select all   Expand viewCollapse view
='file:///C:/Users/fjcc/Desktop/LastValue.ods'#$Sheet1.A1

The source data is in the file LastValue.ods and the cell is Sheet1.A1.

Does that get you what you need?


Thanks for this. I guess I was trying this, but not typing in the info, simply clicking on the cell.
OpenOffice 4 on MacOS Mojave
bbc0
 
Posts: 11
Joined: Wed Oct 09, 2019 5:53 am

Re: Linking spreadsheets

Postby RusselB » Wed Oct 16, 2019 3:42 am

Instead of entering the formula I gave, do the following:
1) Enter =
2) Point to A1 on your current spreadsheet
3) Enter +
4) Switch to the other spreadsheet and point to A1 on that spreadsheet.
5) Press ENTER (aka RETURN)
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Linking spreadsheets

Postby bbc0 » Wed Oct 16, 2019 4:58 am

I actually tried this at first and it definitely doesn't work for me properly. When I look at the formula it looks fine but the links don't seem to update yet sometimes if I close and re-open the sheets they have updated, but not most of the time. Maybe it's something to do with my Mac?
OpenOffice 4 on MacOS Mojave
bbc0
 
Posts: 11
Joined: Wed Oct 09, 2019 5:53 am

Re: Linking spreadsheets

Postby RusselB » Wed Oct 16, 2019 5:11 am

Formulas with links are, imo, and for lack of a better term, quirky.
My recommendation:
1) Create the formula with the links.
2) Save all of the files....even if you've saved them recently, it won't hurt to resave them.
3) Use Edit -> Links -> Update
NOTE: This will only update the links for the current active file. If you want to update the links in the other file(s). you'll have to open/switch to them, then update the links for that specific file.

Personally, I prefer to keep away from linked files, as it makes sharing the file almost impossible.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Linking spreadsheets

Postby bbc0 » Wed Oct 16, 2019 5:14 am

These files are for my own use only. Sharing would be a disaster haha.
Why would they be so quirky though? It's not exactly a new idea :)
OpenOffice 4 on MacOS Mojave
bbc0
 
Posts: 11
Joined: Wed Oct 09, 2019 5:53 am

Re: Linking spreadsheets

Postby RusselB » Wed Oct 16, 2019 6:36 am

No idea... but it's along the same lines of you entering =2+2 in Calc and getting 4, which you would expect.... but, on rare occasions, you might get 3.999999999999999 or 4.0000000000000001
Neither one, of those two, is exactly 4, but I have seen that kind of display.... and 2+2=4 is a much older idea.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Linking spreadsheets

Postby bbc0 » Wed Oct 16, 2019 7:15 pm

RusselB wrote:No idea... but it's along the same lines of you entering =2+2 in Calc and getting 4, which you would expect.... but, on rare occasions, you might get 3.999999999999999 or 4.0000000000000001
Neither one, of those two, is exactly 4, but I have seen that kind of display.... and 2+2=4 is a much older idea.


Reminder to myself: Never allow Calc to perform any calculations hahahahaha So funny
OpenOffice 4 on MacOS Mojave
bbc0
 
Posts: 11
Joined: Wed Oct 09, 2019 5:53 am

Re: Linking spreadsheets

Postby Villeroy » Wed Oct 16, 2019 7:31 pm

bbc0 wrote:
RusselB wrote:No idea... but it's along the same lines of you entering =2+2 in Calc and getting 4, which you would expect.... but, on rare occasions, you might get 3.999999999999999 or 4.0000000000000001
Neither one, of those two, is exactly 4, but I have seen that kind of display.... and 2+2=4 is a much older idea.


Reminder to myself: Never allow Calc to perform any calculations hahahahaha So funny

Same with Excel and any other application that performs decimal floating point arithmetics. There are inaccuraciees due to the conversion between internal bytes and displayed decimals. In most cases the application is smart enough to handle this properly. Sometimes you need explicit rounding as in =ROUND(A1+B1;12)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27745
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Linking spreadsheets

Postby bbc0 » Wed Oct 16, 2019 8:11 pm

Villeroy wrote:
bbc0 wrote:
RusselB wrote:No idea... but it's along the same lines of you entering =2+2 in Calc and getting 4, which you would expect.... but, on rare occasions, you might get 3.999999999999999 or 4.0000000000000001
Neither one, of those two, is exactly 4, but I have seen that kind of display.... and 2+2=4 is a much older idea.


Reminder to myself: Never allow Calc to perform any calculations hahahahaha So funny

Same with Excel and any other application that performs decimal floating point arithmetics. There are inaccuraciees due to the conversion between internal bytes and displayed decimals. In most cases the application is smart enough to handle this properly. Sometimes you need explicit rounding as in =ROUND(A1+B1;12)



Well all I am looking for is consistent updating of links and that's it :)
OpenOffice 4 on MacOS Mojave
bbc0
 
Posts: 11
Joined: Wed Oct 09, 2019 5:53 am

Re: Linking spreadsheets

Postby Villeroy » Wed Oct 16, 2019 8:26 pm

bbc0 wrote:I have 2 spreadsheets AA.ODS and BB.ODS I would like to add the the value in cell A1: AA.ODS to A1: BB.ODS and dipslay the value in A5 in both AA.ODS and BB.ODS and have them linked so that if any of the values are changed in A1 in either .ODS the A5 value in each document will update. I can do this in Excel but can't seem to get it to work in Numbers.
Any help would be appreciated.

We have no idea how you do this in Excel. Calc has 5 different ways to link cells or ranges. Certainly there is no magical quantum cell that exists in 2 sheets simultaneously,
1) ='file:///c:/path/blah.ods'#Sheet.A1 reads data from the source document stored to hard disk, requires manual refresh.
2) =DDE("soffice";"C:\path/blah.ods";"Sheet1.A1") works with range names too, loads both files into memory and updates automatically.
3) menu:Insert>Sheet from file with link option links a whole sheet or csv file.
4) menu:Insert>Link to External Data works with named tables in html and in spreadsheets.
5) Database range linked to a row set of a database document. The database document may be linked to another spreadsheet.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27745
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Linking spreadsheets

Postby bbc0 » Thu Nov 14, 2019 12:20 am

I am still struggling with this. Would you mind explaining way 2) above a little more please.

I currently have the updates working between my two spreadsheets but they only update when I re-open them, not prior to closing. So basically I input my daily values into each spreadsheet the save and exit form each sheet, the I have to reopen each spreadsheet at which time I am asked if I want to update each sheet. I click on 'yes' and the spreadsheets come up with the updated totals.
I do go to edit >Link >update but nothing seems to happen as a result.
OpenOffice 4 on MacOS Mojave
bbc0
 
Posts: 11
Joined: Wed Oct 09, 2019 5:53 am

Re: Linking spreadsheets

Postby RusselB » Thu Nov 14, 2019 1:04 am

If the file AA.ods contains the link, the information comes from the latest saved version of BB.ods
If you update BB.ods, then use Link -> Update with AA.ods, the updated information in BB.ods isn't recognized, since you haven't saved the updated BB.ods
IMO, this is one point where Excel handles linked information better, since it updates all links as soon as the information for those links changes.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Linking spreadsheets

Postby bbc0 » Thu Nov 14, 2019 1:17 am

It doesn't seem to matter I always save my changes but the info never updates until both files have been closed and reopened. It's a pain in the neck.
OpenOffice 4 on MacOS Mojave
bbc0
 
Posts: 11
Joined: Wed Oct 09, 2019 5:53 am


Return to Calc

Who is online

Users browsing this forum: MSN [Bot] and 17 guests