how to get reference to other tables working with field references?

Question

I'd like to create a summary table for tables of months (months are just one possible use of the summary table). The months have some informations and a count column. The summary count of every month should appear in the SUM-table. Like it is working in the following example.

#+TBLNAME: SUM
| Month   | count | other values | notes |
|---------+-------+--------------+-------|
| Jan     |     6 |           33 | blah  |
| Feb     |    15 |           44 | blah  |
|---------+-------+--------------+-------|
| Summary |    21 |              |       |
#+TBLFM: @2$2=remote(Jan,@>$3)::@3$2=remote(Feb,@>$3)
#+TBLFM: @>$2=vsum(@I$2..@II$2)


#+TBLNAME: Jan
| some info | more info | count | notes |
|-----------+-----------+-------+-------|
| foo       | xx        |     1 | blah  |
| bar       | yy        |     2 | blah  |
| baz       | zz        |     3 | blah  |
|-----------+-----------+-------+-------|
| summary   |           |     6 |       |
#+TBLFM: @>$3=vsum(@I$3..@II$3)

#+TBLNAME: Feb
| some info | more info | count | notes |
|-----------+-----------+-------+-------|
| qux       | xx        |     4 | blah  |
| ber       | yy        |     5 | blah  |
| bez       | zz        |     6 | blah  |
|-----------+-----------+-------+-------|
| summary   |           |    15 |       |
#+TBLFM: @>$3=vsum(@I$3..@II$3)

... more months here

Now make the SUM-table more flexible and the table formula better editable. Because doing this: @2$2=remote(Jan,@>$3) for 12 months gets pretty repititive and hard to maintain. So I'd like to replace the part of the SUM-table formula.

I thought about using this SUM-table formula: #+TBLFM: @<<$2..@>>$2=remote($1,@>$3) but that gets me: just an ERROR.

Doing this #+TBLFM: @<<$2..@>>$2=remote($1,$3) gets me the following result in the SUM table:

| Jan     | remote(Jan, 33) |           33 | blah  |
| Feb     | remote(Feb, 44) |           44 | blah  |

This is not what I intended, it should look like this:.

| Jan     |     6 |           33 | blah  |
| Feb     |    15 |           44 | blah  |

How to get remote() properly working?

Answer 1

Or you could have just one facts table, and one summary table. The orgtbl-aggregate package available on Melpa is handy.

#+TBLNAME: Year
| month | some info | more info | count | notes |
|-------+-----------+-----------+-------+-------|
| Jan   | foo       | xx        |     1 | blah  |
| Jan   | bar       | yy        |     2 | blah  |
| Jan   | baz       | zz        |     3 | blah  |
| Feb   | qux       | xx        |     4 | blah  |
| Feb   | ber       | yy        |     5 | blah  |
| Feb   | bez       | zz        |     6 | blah  |
#+TBLFM: @>$4=vsum(@I$4..@II$4)

#+NAME: SUM
#+BEGIN: aggregate :table "Year" :cols "month sum('count')"
| month | sum('count') |
|-------+--------------|
| Jan   |            6 |
| Feb   |           15 |
#+END:

Answer 2

oh, thanks to this answer [https://stackoverflow.com/a/15700076][1] I found a solution.

One working formula would be this:

#+TBLFM: @<<$2..@>>$2='(org-table-get-remote-range $1 (string ?@ ?> ?$ ?3))

Explanation:

1. $1 after org-table-get-remote-range is fetched from the SUM table and is interpreted as the table to be referenced (i.e.: "Jan" for the first row)
2. org-table-get-remote-range seems not to like @>$3 as second parameter in a remote table directly, so you have to assemble this as string with (string ?@ ?> ?$ ?3)
3. 1. and 2. resembles to: for the first line fetch from table Jan the 3. field of the last row
4. the above is done for every row and the result of the above is stuffed into column 2 of the SUM table for every row