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