You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by Ennio Sr <na...@gmail.com> on 2012/09/05 19:33:56 UTC
Spreadsheet links: 1 out of 7 not working...
Hi all!
I need your suggestions to help me possibly detect what may be wrong
in the following situation:
Six different spreadsheet - having the same structure (current account
type) - have a similar 'summary' group of cells where the balances of
all the accounts are shown all together, to have a quick glance of the
whole situation. This is the result of linking each one ss to the
other five (following the instructions given in the OO_3.2 wiki
guide).
These work perfectly.
The seventh link (on each ss) refers to cell D1 of my bank account ss
(where cell D1 refers to 'last_bal' in the same ss and has itself been
given a range name, almost useless).
Now, when I open a spreadsheet and choose to update the links, all
six cc balances appear correctly, whereas the bank balance appears as
'0', although it is not. The formula is the same in all ss, the
Edit/links window shows the same list of connections with the other
files, so I wonder what can cause this strange result.
I have re-established all the links from scratch as the link/window
showed some of them where referring to the original excel files: now
all have extension .ods.
BTW, I have also grouped the seven different ss in a single one,
having seven sheets in it, and it works perfectly with the same
reference to cell D1.
Thanks for your attention.
Regards, Ennio
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]
---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org
Re: Spreadsheet links: 1 out of 7 not working...
Posted by Ennio Sr <na...@gmail.com>.
On Thu, Sep 6, 2012 at 8:04 PM, Dan Lewis <el...@gmail.com> wrote:
[...]
>
> I can see that you would have much learning to do to first learn how
> first to use Base and secondly another database program as well. This seems
> beyond the scope of email.
> --Dan
>
Yes, you're right: it's a long time I do not use OO and I've got a lot
to read and learn...
Anyway, everything is clearer now and I thank you again for your patience.
All the best,
Ennio
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]
---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org
Re: Spreadsheet links: 1 out of 7 not working...
Posted by Dan Lewis <el...@gmail.com>.
Ennio Sr wrote:
> Hi Dan,
> thanks for your reply: it does not solve the problem I encounter but
> gave me useful suggestions. There are a few points I've doubts about:
>
> On Wed, Sep 5, 2012 at 9:02 PM, Dan Lewis <el...@gmail.com> wrote:
>> Ennio Sr wrote:
>>> [...]
>> [...]
>> However, an even better solution is using a database. Have the raw data
>> in the sheets and use Base to connect to the spreadsheet. Create the queries
>> in the database file instead of using spreadsheet functions.
>> Very likely all of the sheets could be combined into a single sheet by
>> adding one more column: the name of the bank account to which that row's
>> data belongs.
> Are you still referring to Calc? (If yes, I do have a spreadsheet with
> all 7 sheets)
Yes, I was referring to Calc, and I understood that you had
placed the seven spreadsheets into one Calc file.
>
>> I had an embedded database
> Maybe I must learn Base to understand what this means?
That might be a good idea. When Base creates a database file, it
is a zipped file containing information about the database. If the data
of the database is contained in the database file, you have an embedded
database. The other possibility is the database file contains
information about where the file or files containing the data are
located and how the data is organized in these files.
>
>> containing all my financial information for
>> 9 accounts. (Then I learned that I could lose data that way,
> That's awful to ear! Do you mean you could lose some or all of your
> data contained in the different sheets kept in one single spreadsheet?
I was talking about Base rather than Calc here. Base keeps data in
memory until it closes the database file. If because some problem
prevents Base from writing this data to the database file, it is lost.
When the data is located outside of the database file, this does not happen.
>
>> so I now keep
>> it in a MySQL backend using Base for the frontend.) It did take some
>> planning and designing, but it is well worth the time spent.
> Did you 'physically' transfer/imported your files so as to create MySQL tables?
> Regards, Ennio
I can see that you would have much learning to do to first learn
how first to use Base and secondly another database program as well.
This seems beyond the scope of email.
--Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org
Re: Spreadsheet links: 1 out of 7 not working...
Posted by Ennio Sr <na...@gmail.com>.
Hi Dan,
thanks for your reply: it does not solve the problem I encounter but
gave me useful suggestions. There are a few points I've doubts about:
On Wed, Sep 5, 2012 at 9:02 PM, Dan Lewis <el...@gmail.com> wrote:
> Ennio Sr wrote:
>> [...]
> [...]
> However, an even better solution is using a database. Have the raw data
> in the sheets and use Base to connect to the spreadsheet. Create the queries
> in the database file instead of using spreadsheet functions.
> Very likely all of the sheets could be combined into a single sheet by
> adding one more column: the name of the bank account to which that row's
> data belongs.
Are you still referring to Calc? (If yes, I do have a spreadsheet with
all 7 sheets)
> I had an embedded database
Maybe I must learn Base to understand what this means?
> containing all my financial information for
> 9 accounts. (Then I learned that I could lose data that way,
That's awful to ear! Do you mean you could lose some or all of your
data contained in the different sheets kept in one single spreadsheet?
> so I now keep
> it in a MySQL backend using Base for the frontend.) It did take some
> planning and designing, but it is well worth the time spent.
Did you 'physically' transfer/imported your files so as to create MySQL tables?
>
Regards, Ennio
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]
---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org
Re: Spreadsheet links: 1 out of 7 not working...
Posted by Dan Lewis <el...@gmail.com>.
Ennio Sr wrote:
> Hi all!
>
> I need your suggestions to help me possibly detect what may be wrong
> in the following situation:
> Six different spreadsheet - having the same structure (current account
> type) - have a similar 'summary' group of cells where the balances of
> all the accounts are shown all together, to have a quick glance of the
> whole situation. This is the result of linking each one ss to the
> other five (following the instructions given in the OO_3.2 wiki
> guide).
> These work perfectly.
> The seventh link (on each ss) refers to cell D1 of my bank account ss
> (where cell D1 refers to 'last_bal' in the same ss and has itself been
> given a range name, almost useless).
>
> Now, when I open a spreadsheet and choose to update the links, all
> six cc balances appear correctly, whereas the bank balance appears as
> '0', although it is not. The formula is the same in all ss, the
> Edit/links window shows the same list of connections with the other
> files, so I wonder what can cause this strange result.
>
> I have re-established all the links from scratch as the link/window
> showed some of them where referring to the original excel files: now
> all have extension .ods.
>
> BTW, I have also grouped the seven different ss in a single one,
> having seven sheets in it, and it works perfectly with the same
> reference to cell D1.
>
> Thanks for your attention.
> Regards, Ennio
>
I think you have found the solution from outward appearances:
updating spreadsheet data links works better when all the data is in one
file. If you are going to use a spreadsheet solution, having the data in
separate sheets of one spreadsheet is a better method.
However, an even better solution is using a database. Have the raw
data in the sheets and use Base to connect to the spreadsheet. Create
the queries in the database file instead of using spreadsheet functions.
Very likely all of the sheets could be combined into a single
sheet by adding one more column: the name of the bank account to which
that row's data belongs.
I had an embedded database containing all my financial information
for 9 accounts. (Then I learned that I could lose data that way, so I
now keep it in a MySQL backend using Base for the frontend.) It did take
some planning and designing, but it is well worth the time spent.
--Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org