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.
To unsubscribe e-mail to: email@example.com
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
Impressum (Legal Info)
: 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