Org mode spreadsheet programmatic remote references

Question

I keep my budget in org-mode and have been pleased with how simple it is. The simplicity fails, however, as I am performing formulas on many cells; for instance, my year summary table that performs the same grab-and-calculate formulas for each month. I end up with a massive line in my +TBLFM. This would be dramatically shorter if I could programmatically pass arguments to the formula. I'm looking for something like this, but working:

| SEPT   |
| #ERROR |
#+TBLFM: @2$1=remote(@1,$tf)

Elsewhere I have a table named SEPT and it has field named "tf". This function works if I replace "@1" with "SEPT" but this would cause me to need a new entry in the formula for every column.

Is there a way to get this working, where the table itself can specify what remote table to call (such as the SEPT in my example)?

Just on the top of my head, you can pass variables from tables to org-babel code blocks, and you may be able to put some elisp in the TBLFM. Combining these may achieve something (I don't know if you can inline org-babel in TBLFM, sounds like a bit of a stretch…), but I don't know of the `remote` function you call for being built-in. I'd love to know the answer though, if you find more about it, please tell us. Maybe I'll gave it a try someday. — Nikana Reklawyks, Dec 02 '12 at 21:42

score 8 · Answer 1 · answered Mar 29 '13 at 08:51

Yes, you can't do this with built-in remote and you need to use org-table-get-remote-range. Hopefully this better suits your needs than the answer given by artscan (I used his/her example):

| testname1 | testname2 |
|-----------+-----------|
|         1 |         2 |
#+TBLFM: @2='(org-table-get-remote-range @<$0 (string ?@ ?1 ?$ ?1))

#+TBLNAME: testname1
|    1 |

#+TBLNAME: testname2
|    2 |

Note the (string ?@ ?1 ?$ ?1): this is necessary because before evaluating table formulae, all substitutions will be done first. If you use "@1$1" directly, it would have triggered the substitution mechanism and be substituted by the contents of the first cell in this table.

score 3 · Answer 2 · answered Jan 01 '13 at 06:02

There is some ugly hack for same effect without using remote:

1) it needs named variable for remote address

(setq eab/test-remote "@1$1")

2) it uses elisp expression (from org-table.el) instead remote(tablename,@1$1)

(defun eab/test-remote (x)
  `(car (read
     (org-table-make-reference
      (org-table-get-remote-range ,x eab/test-remote)
      't 't nil))))

3) worked example

| testname1 | testname2 |
|-----------+-----------|
|           |           |
#+TBLFM: @2='(eval (eab/test-remote @1))

#+TBLNAME: testname1
|    1 |

#+TBLNAME: testname2
|    2 |

4) result

| testname1 | testname2 |
|-----------+-----------|
|         1 |         2 |

Org mode spreadsheet programmatic remote references

2 Answers2