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

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