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

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