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

2012-11-13 Thread Paul Stear
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

2012-11-12 Thread Paul Stear
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

2012-11-11 Thread Paul Stear
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



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

2012-11-08 Thread Paul Stear
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
-- 
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



[libreoffice-users] Cannot save spreadsheets

2011-05-02 Thread Paul Stear
Hi all,
Since upgrading to the latest kubuntu version I have various problems but a 
major one is not being able to save spreadsheets.
I can open them, modify them but when I come to save changes libreoffice just 
crashes and clears the screen.  I thought it might be a permissions problem but 
other openoffice files work correctly.
I am asked each time I open a spreadsheet to recover from the last crash, if I 
do or don't it doesn't make any difference.
I have 3 spreadsheets each with 13 pages and links to each other.

Any experts know how to solve this?

Thanks in anticipation
Paul
-- 
This email has been sent using kmail on kubuntu

-- 
Unsubscribe instructions: E-mail to users+h...@libreoffice.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be deleted


Re: [libreoffice-users] Cannot save spreadsheets

2011-05-02 Thread Paul Stear
On Monday 02 May 2011 18:22:45 planas wrote:
 Paul
 
 On Mon, 2011-05-02 at 16:50 +0100, Paul Stear wrote:
  Hi all,
  Since upgrading to the latest kubuntu version I have various problems but
  a major one is not being able to save spreadsheets.
  I can open them, modify them but when I come to save changes libreoffice
  just crashes and clears the screen.  I thought it might be a permissions
  problem but other openoffice files work correctly.
  I am asked each time I open a spreadsheet to recover from the last crash,
  if I do or don't it doesn't make any difference.
  I have 3 spreadsheets each with 13 pages and links to each other.
  
  Any experts know how to solve this?
  
  Thanks in anticipation
  Paul
 
 Did have this problem with previous kubuntu version?
No this has only happened since the update.
Thanks for your interest
Paul
-- 
This email has been sent using kmail on kubuntu

-- 
Unsubscribe instructions: E-mail to users+h...@libreoffice.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be deleted