Re: [libreoffice-users]

2017-01-08 10:38 GMT+01:00 Vaibhav Banait <vbanait@gmail.com>:

> 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


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

-- 
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