Brian, Thank you so much, I have been away so have not tried your solution yet but it does look good. I will test this in the morning, this could save me hours of work Thank again Paul
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 > 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 -- mail sent using kmail and kubuntu -- For unsubscribe instructions e-mail to: users+h...@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