> On 15 Mar 2024, at 2:58 AM, Ihor Radchenko <yanta...@posteo.net> wrote: > > Wu Ming <wu.mi...@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(...). >
Very clear now. Thank you. But I was mostly confounded by references $0 and #0 versus the @@# (and $$#) you just described the processing of. Don’t want to abuse your time. I can figure it out when needed. But if you feel inclined to unravel this little detail of the manual as well I would clearly appreciate the effort. >> 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. One side effect of using remote formulas is re-organizing columns doesn’t update them automatically. I should find the balance of readability and formulas maintenance cost. But you may have suggested the solution below already with named 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. Clever. And we are at the “Advanced“ features already. Are advanced-advanced in the realm of Calc? Asking because was also wondering how to optimize parameters (“solver”) and deal with locales (“,” vs “.” separators). For the latter I could possibly ‘tr’ them before sharing the output. But will possibly mess the alignment. Happened while trialling groff’s tbl.
