Re: [libreoffice-users] Use of variables in a cell

Thank you very much.  I've got it now.  I thought that a variable was 
placed in the range, and I had to assign a value to that variable.  
Actually is is easier than I thought (often the case). Hope others have 
seen your explanation and have benefited also.

John


On 07/20/2016 11:08 AM, Brian Barker wrote:
> At 10:23 20/07/2016 -0700, John R. Sowden wrote:
> > I think you just introduced another set of questions.
>
> I don't think so ...
>
> > Is the "&" used for macro substitution?
>
> Er, nope: it's the concatenation operator.
>
> > I assume the lowercase n is a shorthand way of defining the variable 
> > type (n=number, s=string, etc.), and not part of the formula?
>
> No: I just used "Xn" to represent the cell that you had referred to 
> but not identified - "at the top of [your] spreadsheet". In your 
> actual formula, this will read A1 or B2 or AP99 or Sheet2.F10 or 
> whatever is the identifier of the cell containing your variable number.
>
> > I went to Help with indirect(), not to much avail. I think I am not 
> > grasping your solution.
>
> Xn contains your variable number. Let's suppose this is 52. The & 
> operator concatenates the string "A1:A" with this, and the number 52 
> is automatically converted to the string "52" for the purpose. The 
> result is the string "A1:A52". Now you may think you can offer this to 
> the SUM() function as a definition of the range of its argument, but 
> that is not so. That would be like using =SUM("A1:A52") - which won't 
> work - instead of the correct =SUM(A1:A52). The problem is that the 
> result of the concatenation is a string "A1:A52" that looks like a 
> cell range but is actually not. The last part of the trick is easy: 
> you just pass that string to the INDIRECT() function and it magically 
> returns the cell range that you need. So the offered solution works. 
> Think of it as the equivalent of =SUM(INDIRECT("A1:A52")) - which may 
> be silly but would work.
>
> > Also, how do I assign the variable value, ..
>
> Er, type the number in the cell. Isn't that what you meant you wanted 
> to do?
>
> > ... and, in terms of sequential execution, where do I put the 
> > assignment so it will be executed before the indirect() function?
>
> Er, what assignment? The value in the (Xn) cell is what you typed 
> there or the result of the formula in it. Providing there is no 
> circularity, the order of execution is irrelevant.
>
> > Is it executed only once? Meaning, if I jump to a cell with my mouse 
> > (manual), is it possible that the indirect() function will fail 
> > because the assignment was not reread?
>
> By "jumping to a cell" do you mean selecting it? That doesn't require 
> any recalculation. If you change the value in any cell, anything 
> dependent on it will normally be recalculated, of course - including, 
> where necessary, the formula in question. That's how spreadsheets 
> operate.
>
> > I am thinking in terms of procedural code here (Basic, Pascal, xBase).
>
> I imagined you were asking about a spreadsheet.
>
> Brian Barker



--
To unsubscribe e-mail to: users+unsubscribe@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