How do I carry over an entire column from one table to another?

Question

I'm trying to set up a monthly budget in org mode, and carry over the balance for each category from month to month. So I'll have something like

|Category|Last |Budget|Actual|Balance|
|        |Month|      |      |       |
|--------+-----+------+------+-------+
|One     |5    |    25|    10|     20|

Where Balance is LastMonth+Budget-Actual, and LastMonth is Balance for Category from the previous table.

I know I can use remote references and have one per category, doing something like @2$2=remote(previous_table,@2$5), and just repeat it over and over for each category, but it seems wasteful and awkward, when I feel like I should be able to use $2=remote(previous_table,$5) to solve the entire problem, but that syntax seems to simply copy over the value in the current table, without using the remote at all.

Is there some way to do what want more effectively?

Answer 1

The formula $2=remote(previous_table,@@#$5) should do the trick. It utilizes the fact that @# is replaced by the current row number (see here for reference):

 #+NAME: previous_table
 | Category |  Last | Budget | Actual | Balance |
 |          | Month |        |        |         |
 |----------+-------+--------+--------+---------|
 | One      |     5 |     25 |     10 |      20 |
 | Two      |     4 |     25 |     10 |      21 |

 | Category |  Last | Budget | Actual | Balance |
 |          | Month |        |        |         |
 |----------+-------+--------+--------+---------|
 | One      |    20 |        |        |         |
 | Two      |    21 |        |        |         |
 #+TBLFM: $1=remote(previous_table,@@#$1)::$2=remote(previous_table,@@#$5)

Note that the category has to come in the same order, thus I would copy them from the other table as well.

Or, alternatively, you can use the category for associative lookup (see here), using 0 as default:

#+NAME: previous_table
| Category |  Last | Budget | Actual | Balance |
|          | Month |        |        |         |
|----------+-------+--------+--------+---------|
| One      |     5 |     25 |     10 |      20 |
| Two      |     4 |     25 |     10 |      21 |

| Category |  Last | Budget | Actual | Balance |
|          | Month |        |        |         |
|----------+-------+--------+--------+---------|
| Two      |    21 |        |        |         |
| One      |    20 |        |        |         |
#+TBLFM: $2='(or (org-lookup-first $1 '(remote(previous_table,@2$1..@>$1)) '(remote(previous_table,@2$5..@>$5))) 0)