Date: prev next · Thread: first prev next last
2016 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.