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 [email protected]
In case of problems unsubscribing, write to [email protected]
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
