Date: prev next · Thread: first prev next last
2017 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.