2016 Archives by date, by thread · List index

# 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).
```
```

```
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
```