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: [email protected]
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
