Open Office Calc problem

Questions on how we spend our money and our time - consumer goods and services, home and vehicle, leisure and recreational activities
Post Reply
User avatar
Topic Author
Earl Lemongrab
Posts: 7270
Joined: Tue Jun 10, 2014 1:14 am

Open Office Calc problem

Post by Earl Lemongrab »

I had posted this on the Open Office forum, but didn't get much response. I thought I'd give the folks here a try. There was one suggestion that didn't work for me, which is included below.

I have a spreadsheet with some formulas for processing my financial data. That uses a sheet produced by an application that I have minimal control over. So my sheet links into various cells on the data sheet.

The problem I have is if there are changes on the data sheet. If an investment is sold, or added, then the positions on this sheet will move around. When I used Excel to manage it, I could open the data sheet and mine, then if I made changes to the data sheet they would generally be reflected in the links. As an example, let's say I have a cell on my sheet linking to the data sheet like:

accounts.xls'#$Sheet1.E118

If I were to insert a blank row above that in the data sheet, the link in mine would automatically change to point to E119. Similarly, if I delete a row, it would link to E117. So I could manage changes by comparing the new and old data sheets to see where rows had changed, then make those changes as dummies in the old sheet. Then close everything and replace the data sheet.

That doesn't seem to work with Calc. If I add or delete a row in the data sheet, then links are pointing to the wrong data and I have to go and update the links to the new locations.

Is there any way handy to do what I'm looking for? Hopefully I 'splained this in a reasonably coherent manner.

Reply:
It sounds to me like you need to change the setting at Tools -> Options -> OpenOffice Calc -> General -> Input settings -> Expand references when new rows/columns are inserted
JBTX
Posts: 11228
Joined: Wed Jul 26, 2017 12:46 pm

Re: Open Office Calc problem

Post by JBTX »

I’ve only used excel so I may not be of much help.

Ideas off the top of my head:

How big is the data sheet? Would it be possible just to copy the entire data sheet to a separate tab in your spreadsheet to get rid of the links? I used to use links a lot, now I avoid them unless absolutely necessary.

Does open office calc have functions like vlookup or Index-match? That way your spreadsheet is looking for data in a linked range instead of a hard link.
User avatar
patrick013
Posts: 3301
Joined: Mon Jul 13, 2015 7:49 pm

Re: Open Office Calc problem

Post by patrick013 »

Earl Lemongrab wrote: Wed Apr 18, 2018 11:44 am
Reply:
It sounds to me like you need to change the setting at Tools -> Options -> OpenOffice Calc -> General -> Input settings -> Expand references when new rows/columns are inserted
Also there's an option to Update Links (should be set to Always) but
I think you might need to hit F9. I'm surprised it doesn't work.
Perhaps it only works when opening the destination file ? The Data
sheet would have to be opened first then the other spreadsheet.
Last edited by patrick013 on Wed Apr 18, 2018 2:06 pm, edited 1 time in total.
age in bonds, buy-and-hold, 10 year business cycle
GAAP
Posts: 2560
Joined: Fri Apr 08, 2016 12:41 pm

Re: Open Office Calc problem

Post by GAAP »

Do you have sufficient access to use named cells on the data sheet? Might work -- haven't actually tried it.
User avatar
dratkinson
Posts: 6116
Joined: Thu Jul 26, 2007 6:23 pm
Location: Centennial CO

Re: Open Office Calc problem

Post by dratkinson »

Can Calc use Excel's named-range feature? Can the source be modified to "name" your needed cells?

Can you copy the whole sheet and use the VLOOKUP option?

Can you copy the whole sheet and use a Calc macro to find your needed cells if VLOOKUP does not work?

Disclosure.
I get daily price data from a 3rd-party website.
--I manually copy the needed data range. (Use <shift><right-click> to mark large data range before <ctrl><c>.)
--An Excel macro pastes it and sorts for me.
--Excel's VLOOKUP runs against fund symbols to extract the NAVs.
d.r.a., not dr.a. | I'm a novice investor; you are forewarned.
bob60014
Posts: 3768
Joined: Mon Jul 31, 2017 8:59 pm
Location: The Land Beyond ORD

Re: Open Office Calc problem

Post by bob60014 »

Be sure you have a backup of the original and keep it safe before you start playing! :)
kithwang
Posts: 154
Joined: Tue Jul 22, 2014 8:35 am

Re: Open Office Calc problem

Post by kithwang »

Uninstall and reinstall or go to another computer. I don't have that problem. You may have changed permissions somewhere.
User avatar
Topic Author
Earl Lemongrab
Posts: 7270
Joined: Tue Jun 10, 2014 1:14 am

Re: Open Office Calc problem

Post by Earl Lemongrab »

JBTX wrote: Wed Apr 18, 2018 12:10 pm I’ve only used excel so I may not be of much help.

Ideas off the top of my head:

How big is the data sheet?

Not very big. It's the output of an aggregator. Somewhere on the order of 30 rows.

Would it be possible just to copy the entire data sheet to a separate tab in your spreadsheet to get rid of the links? I used to use links a lot, now I avoid them unless absolutely necessary.

I guess I could try that. You'd still have links to cells in the other tab. Do they adjust automatically then?

Does open office calc have functions like vlookup or Index-match? That way your spreadsheet is looking for data in a linked range instead of a hard link.

I don't know about that. What I'm doing is picking out the symbol name and current holding value to use for other purposes.
User avatar
Topic Author
Earl Lemongrab
Posts: 7270
Joined: Tue Jun 10, 2014 1:14 am

Re: Open Office Calc problem

Post by Earl Lemongrab »

patrick013 wrote: Wed Apr 18, 2018 12:48 pm
Earl Lemongrab wrote: Wed Apr 18, 2018 11:44 am
Reply:
It sounds to me like you need to change the setting at Tools -> Options -> OpenOffice Calc -> General -> Input settings -> Expand references when new rows/columns are inserted
Also there's an option to Update Links (should be set to Always) but
I think you might need to hit F9. I'm surprised it doesn't work.
Perhaps it only works when opening the destination file ? The Data
sheet would have to be opened first then the other spreadsheet.
Update links only pulls fresh data. It doesn't change the link. See my example. Excel would detect changes to the source sheet and actually change where the links were pointing.
User avatar
Topic Author
Earl Lemongrab
Posts: 7270
Joined: Tue Jun 10, 2014 1:14 am

Re: Open Office Calc problem

Post by Earl Lemongrab »

GAAP wrote: Wed Apr 18, 2018 1:03 pm Do you have sufficient access to use named cells on the data sheet? Might work -- haven't actually tried it.
The cells aren't named. Again, the sheet is created by another app, an aggregtor. I can control what it outputs to a degree, and I can sort the data etc. But that's about it.
User avatar
Topic Author
Earl Lemongrab
Posts: 7270
Joined: Tue Jun 10, 2014 1:14 am

Re: Open Office Calc problem

Post by Earl Lemongrab »

bob60014 wrote: Wed Apr 18, 2018 1:35 pm Be sure you have a backup of the original and keep it safe before you start playing! :)
Well, sure. In fact each month I just copy the entire folder to a new one before I start updating.
User avatar
Topic Author
Earl Lemongrab
Posts: 7270
Joined: Tue Jun 10, 2014 1:14 am

Re: Open Office Calc problem

Post by Earl Lemongrab »

kithwang wrote: Wed Apr 18, 2018 1:59 pm Uninstall and reinstall or go to another computer. I don't have that problem. You may have changed permissions somewhere.
I don't understand. Do you actually understand my problem? Can you describe it back to me?
User avatar
Topic Author
Earl Lemongrab
Posts: 7270
Joined: Tue Jun 10, 2014 1:14 am

Re: Open Office Calc problem

Post by Earl Lemongrab »

JBTX wrote: Wed Apr 18, 2018 12:10 pm Would it be possible just to copy the entire data sheet to a separate tab in your spreadsheet to get rid of the links? I used to use links a lot, now I avoid them unless absolutely necessary.
That looks very promising. I did an experiment and it seemed to do what I wanted. That shouldn't be too bad of a change. The cell numbers all stay the same, so I should be able to update the links with search/replace.

Thanks.
FactualFran
Posts: 2777
Joined: Sat Feb 21, 2015 1:29 pm

Re: Open Office Calc problem

Post by FactualFran »

Not adjusting cell addresses that refer to another file when rows or columns are added or deleted may be a "feature". I get the same behavior.

When I copy a cell with a reference to another file, the relative row and column values in the reference get adjusted when pasted. For example, copying a cell containing a reference like =accounts.xls'#$Sheet1.E118 and pasting it one column to the right and one row down results in the reference =accounts.xls'#$Sheet1.F119.

That suggests a workaround: copy-and-paste those references instead of only adding or deleting rows or columns
Mudpuppy
Posts: 7409
Joined: Sat Aug 27, 2011 2:26 am
Location: Sunny California

Re: Open Office Calc problem

Post by Mudpuppy »

Earl Lemongrab wrote: Wed Apr 18, 2018 3:08 pm
JBTX wrote: Wed Apr 18, 2018 12:10 pm Would it be possible just to copy the entire data sheet to a separate tab in your spreadsheet to get rid of the links? I used to use links a lot, now I avoid them unless absolutely necessary.
That looks very promising. I did an experiment and it seemed to do what I wanted. That shouldn't be too bad of a change. The cell numbers all stay the same, so I should be able to update the links with search/replace.

Thanks.
This is likely to be your best path to success. If I've read your original problem correctly, you are wanting to update references in File A when File B changes, and I could see why Calc may not monitor for that (e.g. what would it do if File A was closed when File B changed). Calc does monitor for updating references in Tab A when Tab B changes, where both tabs are in the same file. So putting the data and formula within the same file should let you do the modifications you wish to do without having to learn something new like vlookup().
User avatar
Topic Author
Earl Lemongrab
Posts: 7270
Joined: Tue Jun 10, 2014 1:14 am

Re: Open Office Calc problem

Post by Earl Lemongrab »

Mudpuppy wrote: Thu Apr 19, 2018 1:05 am
Earl Lemongrab wrote: Wed Apr 18, 2018 3:08 pm This is likely to be your best path to success. If I've read your original problem correctly, you are wanting to update references in File A when File B changes, and I could see why Calc may not monitor for that (e.g. what would it do if File A was closed when File B changed). Calc does monitor for updating references in Tab A when Tab B changes, where both tabs are in the same file. So putting the data and formula within the same file should let you do the modifications you wish to do without having to learn something new like vlookup().
That's the goal. It's something Excel would do, so I used to make the updates at work when I had access to it. Now I use Open Office exclusively, so I needed to get a way to do it. This looks like it will work.
SittingOnTheFence
Posts: 305
Joined: Sun Sep 27, 2015 5:30 pm

Re: Open Office Calc problem

Post by SittingOnTheFence »

Have you checked to see if you have latest version of OO?
User avatar
Topic Author
Earl Lemongrab
Posts: 7270
Joined: Tue Jun 10, 2014 1:14 am

Re: Open Office Calc problem

Post by Earl Lemongrab »

SittingOnTheFence wrote: Thu Apr 19, 2018 12:50 pm Have you checked to see if you have latest version of OO?
I don't, but I'm close.
Post Reply