0

With Table>Set Field>Formula, I'm able to do what I want for a particular field, in this case that corresponding to Aug 07. How can I fill multiple adjacent fields in one go, say Aug 7-15?

#+TITLE: Ledger
#+CONSTANT: r=10


| Date                 | Owed |
|----------------------+------|
| [2022-08-06 Sat]     |   10 |
| [2022-08-06 Sat] + d |   20 |
#+TBLFM: @3$1=@2$1+1d::@3$2=@2$2+10

1 Answer 1

0

The following table/formula should do what you want IIUC:

| Date             | Owed |
|------------------+------|
| [2022-08-06 Sat] |   10 |
|                  |      |
|                  |      |
|                  |      |
|                  |      |
|                  |      |
|                  |      |
|                  |      |
|                  |      |
|                  |      |
|                  |      |
#+TBLFM: @3$1..@>$1=@-1$1+1::@3$2..@>$2=@-1$2+10

You need to have enough rows for what you want: the formula will fill however many you actually have.

The formula reads: row 3 to the last row, column 1: use the date from the entry above the current row and the first column(@-1$1), and add one day to it. The second part is similar but it adds 10 to the entry from the previous row and the second column.

Note that I did not use the d: calc then calculates dates properly. You might also find S-RET useful when filling in tables with dates: you get the first date in and then keep pressing S-RET for each next day, however many times you wish.

Relevant sections of the manual:

References - see particularly the Range references subsection.

Field references in the same section as above but in the Field references subsection describes relative references like @-1.

Built-in Table Editor - see the Calculations subsection for S-RET.

EDIT: There are two formulas on the #+TBLFM: line: the first one, @3$1..@>$1=@-1$1+1, fills in the first column as indicated by its range: third to last row, first column; the second, @3$2..@>$2=@-1$2+10, fills in the second column: its range is third to last row, second column. You can also write formulas for rectangular ranges consisting of multiple rows and columns, e.g. @2$1..@>$> = 0 would initialize a table to 0 (we leave the first row alone, assuming it contains headers).

To evaluate the formulas, just C-c C-c on the #+TBLFM: line.

Note that in order for the formula to work the way you want, you must have enough empty rows for your criteria (e.g. to fill in from Aug 6 to Aug 15, you must have 10 rows in the body of the table), but instead of counting, I prefer to create the table with more rows than necessary and trim off the excess after the formula has been applied.

If you want a hybrid approach where you use S-RET for the first column to manually create it and then use a formula for the second column, just keep the second formula in the #TBLFM: line:

| Date             | Owed |
|------------------+------|
| [2022-08-06 Sat] |   10 |
| [2022-08-07 Sun] |      |
| [2022-08-08 Mon] |      |
| [2022-08-09 Tue] |      |
| [2022-08-10 Wed] |      |
| [2022-08-11 Thu] |      |
| [2022-08-12 Fri] |      |
| [2022-08-13 Sat] |      |
| [2022-08-14 Sun] |      |
| [2022-08-15 Mon] |      |
#+TBLFM: @3$2..@>$2=@-1$2+10

C-c C-c on the #+TBLFM: line will leave the first column untouched and fill in the second column.

4
  • If I do S-RET under Owed in the second row, I get $11 instead of $20...
    – user19777
    Aug 10, 2022 at 15:36
  • That' correct: the default increment is 1. That works well with the dates (if you want to increment by days), but it doesn't work with other increments. You can set the value of org-table-copy-increment to 10 before you do the second column, but it's easier to use the formula. But I often set it to 7 before doing S-RET on a dates column, if I want to increment by weeks, rather than by days. Note that in a non-numeric, non-date column, S-RET copies down the previous entry, so it's a quick way to fill in a text column where the entries are constant for longish stretches.
    – NickD
    Aug 10, 2022 at 16:18
  • The formula you created is supposed to increment rows in the second column by $10, right? How do I fill the rows using this formula?
    – user19777
    Aug 10, 2022 at 16:26
  • Edited the answer to add some more info: HTH.
    – NickD
    Aug 10, 2022 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.