At 11:06 08/01/2017 +0100, Cley Faye wrote:
2017-01-08 10:38 GMT+01:00 Vaibhav Banait:
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.B25
Assuming 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).
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
--
To unsubscribe e-mail to: [email protected]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
