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


On 14/07/11 03:13, Thorsten Kampe wrote:
Hi,

I have a Calc file which contains multiple sheets.

- every sheet contains one month of data: from May 2010 to July 2011.

- every sheet has two columes: in column A the days and in column B the
actual data.

For example:

[July 2011 Sheet]
   A      B
1 07/02  75.6
2 07/03
3
4 07/04  76.4
5 07/05  76.3
6 07/06  76.2
7 07/07
8 07/08  75.6

The data entries are sometimes empty (07/07 for example), do not always
start on the 1st and there are some empty rows in between.

Problem 1: I want to create a chart of all sheets/months. What do I have
to enter for Date Range in step 2 of the chart wizard (pre-selected is
$'July 2011'.$A$1:$B$3)?

Problem 2: I'd like to automatically create this chart every day and
export it to a PNG file. Is that doable via LibreOffice Calc?

Thanks in advance, Thorsten


Thorsten,

This is how I would address this problem

Sheet 1 would include all data (column A & B) for every month.

Sheet 2 will be the monthly summary sheet.

In this sheet we create a stylised month.

Cell A1 and B1 would be a picklist of months or years. Use data > validity > Criteria to set up a list or point to a allowable range.

In row A3:AE3 create the numbers 1-31 which represents the day of the month.

Then underneath each day in A4:AE4, use the Vlookup() function to extract the data for each day of the month and year selected.

The function would look like this...
=VLOOKUP(DATE($A$1,$B$1,A3),Sheet1.YourRange,2,0)

to get rid of the annoying situation where absent values throw and error use the following to capture them

=IF(ISERROR(VLOOKUP(DATE($A$1,$B$1,A3),Sheet1.YourRange,2,0)), "", VLOOKUP(DATE($A$1,$B$1,A3),Sheet1.YourRange,2,0))

This will create an idealised month with values populated based on what month and year you select.

Create your chart based on this.

In regards to exporting as a PNG, several options exist if you are using Windows...

1. use a PDF printer driver that exports to PNG, rather than the inbuild export utility like PDFCreator
http://sourceforge.net/projects/pdfcreator/

2. Use an ImageCreating printer driver like ImagePrinter
http://sourceforge.net/projects/imageprinter/

Doing the same in GNU/Linux requires you to install a better PDF Printer Driver than allows you to save as an image or post-process the PDF using another program.

Once you set this up to work manually create a macro and attach it to a button or something.

--
Cheers Simon

   Simon Cropper
   Website Administrator
   http://www.fossworkflowguides.com
   The fossWorkflow Guides
   (c) Simon Cropper CC-BY-SA 3.0 Australia
   http://creativecommons.org/licenses/by-sa/3.0/au/deed.en

--
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
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.