Re: [libreoffice-users] Use of variables in a cell
At 13:36 20/07/2016 -0700, John R. Sowden wrote: Thank you very much. No probs! I've got it now. I thought of explaining it all the first time around. Not sure why I didn't. Actually it is easier than I thought (often the case). Indeed so. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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] 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+unsubscr...@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] 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+unsubscr...@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] Use of variables in a cell
I think you just introduced another set of questions.
Is the "&" used for macro substitution? If so, how does that fit in?
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?
I went to Help with indirect(), not to much avail. I think I am not
grasping your solution. Also, how do I assign the variable value, and,
in terms of sequential execution, where do I put the assignment so it
will be executed before the indirect() function?
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? (I am thinking in terms of procedural
code here (Basic, Pascal, xBase).
John
On 07/20/2016 09:35 AM, Brian Barker wrote:
At 08:50 20/07/2016 -0700, John R. Sowden wrote:
If I have a cell with following formula, can I replace a cell
coordinate, such as the 52 below, with a variable that I can assign
at the top of my spreadsheet?
=sum(a1:a52)
Yes:
=SUM(INDIRECT("A1:A"&Xn))
I trust this helps.
Brian Barker
--
To unsubscribe e-mail to: users+unsubscr...@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] Use of variables in a cell
At 08:50 20/07/2016 -0700, John R. Sowden wrote:
If I have a cell with following formula, can I replace a cell
coordinate, such as the 52 below, with a variable that I can assign
at the top of my spreadsheet?
=sum(a1:a52)
Yes:
=SUM(INDIRECT("A1:A"&Xn))
I trust this helps.
Brian Barker
--
To unsubscribe e-mail to: users+unsubscr...@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] Use of variables in a cell
If I have a cell with following formula, can I replace a cell coordinate, such as the 52 below, with a variable that I can assign at the top of my spreadsheet? =sum(a1:a52) Thanks, John -- To unsubscribe e-mail to: users+unsubscr...@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] extracting data from uri
Right, I saw it now. I guess it has been 80% my mistake, because the documentation says HTML files (at the end I completed the project using an html data source and it works fine). However, I saw there is also a possibility of insert data in a json format (I did something, I don't know how). Data can be read in some cases and some way, but not used properly. I reported it as a bug, maybe it is not exactly, but the matter could be of some interest. Let's see. Thanks for your advice. Paolo On Wednesday, July 20, 2016 7:58 AM, Luuk wrote: On 18-07-16 18:20, Paolo Debortoli wrote: > Hi. I am working on a calc sheet and need to insert data from an url: > The data you see is in JSON format https://en.wikipedia.org/wiki/JSON someone tried to make an extension for it 2 years ago, but it seems to be not released right now: http://extensions.libreoffice.org/extension-center?path=%2FLibreOffice-Extensions-and-Templates%2Fextension-center&portal_type=PSCProject&SearchableText=json -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
