Re: [libreoffice-users] Auto changes on a new spreadsheet set for next year

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 automatically.)

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")

I trust this helps.

Brian Barker


--
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