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

Thank you again Brian, yesterday I tried all-sorts to try and get this 
working, looking at your solution I was nearly there. -- So much to learn.
What is the best source for this sort of information?
I would like to learn more and make my spreadsheets more efficient.
I must admit that the help pages were a bit daunting and I couldn't relate the 
example given to my problem.

kind regards

On Tuesday 13 Nov 2012 05:00:05 Brian Barker wrote:
At 14:42 12/11/2012 +0000, Paul Stear wrote:
On Thursday 08 Nov 2012 14:19:22 Brian Barker wrote:
At 11:45 08/11/2012 +0000, Paul Stear wrote:
I have the same spreadsheet set I use each year.  The new
spreadsheet for 2013 will need to reference fields in the 2012
spreadsheet.  For the past few years I have manually changed the
Readings-2012 in every instance (well over 100).

I would like to be able to construct the spreadsheets with a
method to change the current year, eg 2013 minus 1 to give 2012
inserted so that the ref in the 2013 spreadsheet reads;-
='file:///home/fred/Generation Readings-2012.ods'#$Jan.A68

Is this possible?

Yes.  You need to concatenate the required year value with the
strings required before and after it.  Suppose that you have the
current year - 2013 in your example - in A1 of your new
spreadsheet.  You need to concatenate
"'file:///home/fred/Generation Readings-" with A1-1 and
".ods'#$Jan.A68".  Note that the two single quotes are part of the
string you are creating and the four double quotes delimit the two
text strings, so the first string has a single quote immediately
following its opening double quote.  You can carry out this
concatenation using the & operator:
="'file:///home/fred/Generation Readings-"&A1-1&".ods'#$Jan.A68"
(The numerical expression A1-1 is converted to a string value

You might expect this to work, but it doesn't.  The formula above
results in a text string which is interpreted literally and not as
a cell reference to the other spreadsheet file.  But the trick you
need is available in the INDIRECT() function, which provides the
necessary conversion.  So the formula which works is
=INDIRECT("'file:///home/fred/Generation Readings-"&A1-1&".ods'#$Jan.A68")

Hi Brian,
Thank you so much, your solution works a treat.

My spreadsheet consists of 15 pages each year, so my next question
is:- would it be possible to put the new year on page 1 named
"cover" in cell A2 and then change the "&A1-1&" to reference this on
for each instance on every page?

Yes.  You refer to a cell on another sheet as sheet.cell.  So you
would just need to use ...&cover.A2-1&... .

Brian Barker
mail sent using kmail and kubuntu
For unsubscribe instructions e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


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.