Re: [the cryptic @@#$7]

[Uwe Brauer]

> Uwe Brauer <oub@mat.ucm.es> writes:

> The ref "remote(table1,@2$7..@>$7)" targets a range, containing all fields of
> colum 7.  So, for each field, you get the same value: an array
> containing all the field values: [22, 19, 19].



> See (info "(org) References"), in the section:
>    "Field coordinates in formulas"

> "‘@#’ is substituted with the row number of the field where the
> current result will go to".



> With this formula, your remote reference targets the field, that is in
> the same row number as the computed field in final.

> Here is the same table, with some extra columns that show the
> intermediate steps:

>    #+Name: final2
>    | Name   | Some | ResSh1 | What is '@#'? | Which ref? |

>    |--------+------+--------+---------------+------------|
>    | Smith  |    4 |     22 |             2 | @2$7       |
>    | Miller |    4 |     19 |             3 | @3$7       |
>    | Wick   |    4 |     18 |             4 | @4$7       |
>    |--------+------+--------+---------------+------------|

>    #+TBLFM: @2$3..@>$3=remote(table1,@@#$7)
>    #+TBLFM: @2$4..@>$4=@#

>    #+TBLFM: @2$5..@>$5='(concat "@" (format "%d" @#) "$" "7")


Here is an example where the org-lookup-first method seems to fail

#+begin_src 

#+Name: Table1A
| / | <>       |  <> |  <> |  <> |  <> |  <> |    <> |
|   | Name     | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | E1Tot |
|---+----------+-----+-----+-----+-----+-----+-------|
|   | Smith    |   2 |   3 |   4 |   6 |   7 |    22 |
|   | Brown    |   7 |  10 |   3 |   4 |  12 |    36 |
|   | Graham   |   4 |   5 |   1 |  10 |   7 |    27 |
|   | Jones    |  14 |   9 |   1 |   8 |  13 |    45 |
|   | Thompson |  14 |   4 |  11 |   9 |  11 |    49 |
|   | Walker   |   3 |  11 |   5 |   3 |   6 |    28 |
#+TBLFM: $8=vsum($3..$7);f2

#+Name: Table2A
| / | <>       |  <> |  <> |  <> |  <> |  <> |    <> |
|   | Name     | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | E2Tot |
|---+----------+-----+-----+-----+-----+-----+-------|
|   | Smith    |   1 |   3 |   5 |   8 |   9 |    26 |
|   | Brown    |  14 |   9 |  10 |   3 |   8 |    44 |
|   | Graham   |   5 |  14 |   1 |   5 |   6 |    31 |
|   | Jones    |  11 |  11 |   3 |   1 |   9 |    35 |
|   | Thompson |   7 |  10 |   3 |   9 |   5 |    34 |
|   | Walker   |   2 |   3 |   3 |  10 |   2 |    20 |
#+TBLFM: $8=vsum($3..$7);f2


#+Name: Final1A
| / | <>       | <>    | <>    | <>    |
|   | Name     | E1Tot | E2Tot | Total |
|---+----------+-------+-------+-------|
|   | Smith    | nil   | nil   | 2 nil |
|   | Brown    | nil   | nil   | 2 nil |
|   | Graham   | nil   | nil   | 2 nil |
|   | Jones    | nil   | nil   | 2 nil |
|   | Thompson | nil   | nil   | 2 nil |
|   | Walker   | nil   | nil   | 2 nil |
#+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$1..@II$1)) 
'(remote(Table1A, @I$7..@II$7)))::$4='(org-lookup-first $2 '(remote(Table2A, 
@I$1..@II$1)) '(remote(Table2A, @I$7..@II$7)))::$5=vsum($3..$4);f2


#+Name: Final2A
| / | <>       |    <> |    <> |    <> |
|   | Name     | E1Tot | E2Tot | Total |
|---+----------+-------+-------+-------|
|   | Smith    |    22 |    26 |    48 |
|   | Brown    |    36 |    44 |    80 |
|   | Graham   |    27 |    31 |    58 |
|   | Jones    |    45 |    35 |    80 |
|   | Thompson |    49 |    34 |    83 |
|   | Walker   |    28 |    20 |    48 |
#+TBLFM: 
$5=vsum($3..$4);f2::@3$3..@>$3=remote(Table1A,@@#$8)::@3$4..@>$4=remote(Table2A,@@#$8)




#+Name: Final3
| / | <>       |    <> |    <> |    <> |
|   | Name     | E1Tot | E2Tot | Total |
|---+----------+-------+-------+-------|
|   | Smith    |    22 |    26 |    48 |
|   | Brown    |    36 |    44 |    80 |
|   | Graham   |    27 |    31 |    58 |
|   | Jones    |    45 |    35 |    80 |
|   | Thompson |    49 |    34 |    83 |
|   | Walker   |    28 |    20 |    48 |
#+TBLFM: $3=remote(Table1A,@@#$8)::$4=remote(Table2A,@@#$8)::$5=vsum($3..$4);f2
#+end_src






> Bruno






-- 
Warning: Content may be disturbing to some audiences
I strongly condemn Hamas terroristic attack on Israel.
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military. 
I support the NATO membership of Ukraine.
I support the EU membership of Ukraine. 
https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/

smime.p7s
Description: S/MIME cryptographic signature