[libreoffice-users]

users
#1

I use Calc to do invoicing. I use one sheet per invoice and one calc file per day. I calculate total amount invoiced using formula =Sum (sheet2.B25:sheet16:B25). I generate 25 files per month x 12 month. Is there a way I can calculate total amount invoiced in a year by using some formula by parsing the files considering the total of the day is on sheet1.B25Kindly help 
Vaibhav

#2

A
​ssu​ming you have some sort of naming convention for your files, it is
easy to do. You can reference other files from within a sheet.

Here are two way to do it with two "day" file and a "summary" file.
Assuming the total in the day files is on sheet1.A1.

First solution is to use these formula:

='file:///E:/day1.ods'#$Sheet1.A1
='file:///E:/day2.ods'#$Sheet1.A1

They will pull data from the file given in reference.
But as you see, you have to put the file path in full (relative URL won't
work). There's a way to easily make this scalable to many days if you use
INDIRECT(). In your "summary" file, put the file path somewhere (in my
example it'll be E1) and the day file names in a col (for example C:C),
then use these formula to get your results:

=INDIRECT("'file:///"&E$1&C1&".ods'#$Sheet1.A1")
=INDIRECT("'file:///"&E$1&C2&".ods'#$Sheet1.A1")

that way you can quickly get the results from many days, and if you have to
move your files around you only have to update a single cell instead of all
of them.

This should give you a good example on how to achieve this:
http://www.cjoint.com/c/GAikfTZMQFc

#3

Are you sure that relative URLs don't work? I think there are two ways in which this is not quite true.

o First, it should be possible to insert a relative reference in the hyperlink in the cell, e.g. just the name of a referenced file in the same folder:
='day1.ods'#$Sheet1.A1
Calc will then expand that to an absolute reference for you.

o Secondly, and possibly more interestingly, it is possible to get Calc to save relative references in the document file. Go to Tools | Options... | Load/Save | General | Save. Remove the tick from "Save URLs relative to file system". This way, referenced document files will continue to be found if they are positioned similarly relative to the document containing the hyperlink. Note that - somewhat confusingly - Calc always expands relative references and displays absolute references in formulae, e.g. in the Input Line, which may give the mistaken impression that the relative addressing is not working.

Brian Barker