Re: [libreoffice-users] Auto changes on a new spreadsheet set for next year
On 11/08/2012 06:45 AM, 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. ='file:///home/fred/Generation Readings-2012.ods'#$Jan.A68 The 2012 spreadsheet is referenced to 'file:///home/fred/Generation Readings-2011.ods'#$Jan.A68 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? Thanks for any help regards Paul I don't know if you attached a file with your email or not. If you did, it was stripped by the mailing list. So, in other words, no one on the list can access a file to see what you describe in a Calc file. Since I'm into databases, I'm wondering if your data would be better served creating a database to hold it. Not knowing how many sheet your file contains nor what the columns are and what format each column use. (I'm guessing that you use one sheet per year.) So, it would be impossible to make any detailed comments. From personal experience, a database would require less time to maintain than your present speadsheet. With a little bit of work, all your data could be entered into a database. Could we have more information, please? --Dan -- 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
Re: [libreoffice-users] Auto changes on a new spreadsheet set for next year
At 11:45 08/11/2012 +, 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+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
Re: [libreoffice-users] Auto changes on a new spreadsheet set for next year
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 +, 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
Re: [libreoffice-users] Auto changes on a new spreadsheet set for next year
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? Hope this is clear. Thanks for any help Paul On Thursday 08 Nov 2012 14:19:22 Brian Barker wrote: > At 11:45 08/11/2012 +, 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
Re: [libreoffice-users] Auto changes on a new spreadsheet set for next year
At 14:42 12/11/2012 +, Paul Stear wrote: On Thursday 08 Nov 2012 14:19:22 Brian Barker wrote: At 11:45 08/11/2012 +, 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 -- 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
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 +, Paul Stear wrote: > >On Thursday 08 Nov 2012 14:19:22 Brian Barker wrote: > >>At 11:45 08/11/2012 +, 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
Re: [libreoffice-users] Auto changes on a new spreadsheet set for next year
Hi :) You might have more fun with the official guides wiki.documentfoundation.org/Documentation/Publications particularly the Calc Guide. There are some 3rd party guides near the end of the page and other things that might be interesting. Google might help you find some HowTo videos. Happy hunting! Regards from Tom :) > > From: Paul Stear >To: users@global.libreoffice.org >Sent: Tuesday, 13 November 2012, 16:06 >Subject: 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 +, Paul Stear wrote: >> >On Thursday 08 Nov 2012 14:19:22 Brian Barker wrote: >> >>At 11:45 08/11/2012 +, 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 > > > > -- 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