Re: [libreoffice-users] Help with Calc

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: users+unsubscribe@global.libreoffice.org
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