Re: Table column formula with remote reference

[Ihor Radchenko <yantar92@posteo.net>]

Wu Ming <wu.ming2@icloud.com> writes:

>> See "Remote references" subsection. It explains that in
>> remote(NAME,REF), REF is inside the remote table. Relative and current
>> column/row is ambiguous there.
>> 
>> In contrast, @# and $# are special - they are replaced before
>> remote(...) is processed.
> ...
> I have some trouble at understanding your answer. Do you mean @# refers a row on the table where the formula belongs and @0 refers a row on the remote table? Was tempted to describe the former as “current” but remote table is also current when accessed. A better noun may be needed.

Let me elaborate.

When Org mode sees something like

#+TBLFML: $1 = $2 + remote(A,@@#$1) 

1. it goes to every cell in column 1 and remembers current column and
   row numbers (original cell)

2. In the right side of the formula $2 + remote(A,@@#$1), Org replaces
   all the instances of @# and $# with current column and row.
   So, when we are calculating the value for @1$1, we get
   $2 + remote(A,@1$1)

3. Org moves to table A and replaces remote(A,@1$1) with cell contents
   of @1$1 inside table A. At this point, it is not allowed to have
   relative references like $1 or $-1, because "current" column and row
   are set inside remote table A - the original cell coordinates are not
   available.

4. Org goes back to the original table, takes the updated formula
   $2 + <remote value A@1$1>, and replaces relative reference $2
   according to the current column - with the value stored in @1$2
   column

5. Org passes the resulting expression <local value @1$2> + <remote
   value A@1$1> to GNU cal and assigns the result as the value of the
   current cell @1$1.

6. Repeat for @2..$1 cells.

As you can see, @# and $# substitution always uses local cell
coordinates. Any other relative reference is not allowed inside
remote(...).

> This made me worry about reliability of simple biz calculations I am trying on Org spreadsheet for the first time. Please advise.

Formula debugger is really helpful to understand the process.

> Finally I moved columns but now column numbers in formulas don’t relate to column order on display. How to understand which column formula affect which column?

Normally, if you use org-table-* commands, the formulas get updated when
you move the columns.

To make things more readable, you can also assign names to columns:

     | ! |         |     P1 |     P2 |     P3 |   Tot |      |
     |   | Maximum |     10 |     15 |     25 |    50 | 10.0 |

Then, you can write $P1 = ... instead of $3 = ...
See "3.5.10 Advanced features" section of the manual.

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>