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


Dne 3.2.2012 16:50, Andreas Säger napsal(a):
Am 03.02.2012 16:05, Gordon Burgess-Parker wrote:
I have a spreadsheet that is used for monthly and annual collection of
data, with monthly tabs, Jan 2011, Feb 2011 etc.
Is there a way to rename 2011 to 2012 in bulk, or do I have to do each
one manually?


Splitting up equally structured data across many sheets is *always* a huge mistake. Don't do that.
Simply put everything in one table and add a field for the month.
There are dozends of features which allow you to get data and calculations for any category of a single table.



Listen to that smart advice from Andreas.
And do it better by adding another field (column) for year and line_ID.

That line_ID I mean something that identifies the line content.
Let's say you have in each sheet report line labeled "Turnover", line labeled "Costs"
and line labeled "Number of employes".
Those line labels can be the line_ID but better is to add a numeric key because of sorting.

Then use the data-pilot to filter and select desired year and month.
The final report you can edit and format using the marvelous VLOOKUP function
- lookup in the data-pilot values belonging to line_IDs.

Resume:
- keep unformatted data in Sheet1 - this is your database

- have selected time period filtered in datapilot in Sheet2 - define once, change filter only

- format a nice presentation of v-looked-up values in Sheet3 - define once - it presents data of Sheet2


Regards,
Jiri

--

Ing. Jiří Hladůvka - REVIDA

http://www.revida.sk
mailto:mail@revida.sk


--
For unsubscribe instructions e-mail to: users+help@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.