Open kjambunathan opened 1 year ago
I made some progress in understading both the ODS and the org side.
I start with the ods side. There are the following possibilities to link tables that are located on different sheets
To cite Remy
My solution used =SUMIF()- which allows for multiple entries of, for example, Smith in the same table. (That is the solution I send you privately)
VLOOKUP() works perfectly for your current setup. It will, however, only return the first occurrence of Smith if there are many. This is therefore something to consider if your table expands in the future (perhaps by adding a new column for date references).
Your solution is indeed the most simple, but it will become difficult to maintain if the names in the various tables are not in the same order or if the names are different from table to table. I usually prefer using search functions (like MATCH(), VLOOKUP(), or SUMIF()) because they remove the need of maintaining the formula references if there are insertions or deletions in the source data. They will also allow you to present your summary table in a different order by only changing the content of the cells in column "A".
There are at least solutions, thanks to Bruno Barbier
#+Name: Table1
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS |
|----------+-----+-----+-----+-----+-----+------|
| 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: $7=vsum($2..$6);f2
#+Name: Table2
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS |
|----------+-----+-----+-----+-----+-----+------|
| 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: $7=vsum($2..$6);f2
#+Name: Final
| Name | Table1 | Table2 | 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: $2='(org-lookup-first $1 '(remote(Table1, @I$1..@II$1)) '(remote(Table1, @I$7..@II$7)))::$3='(org-lookup-first $1 '(remote(Table2, @I$1..@II$1)) '(remote(Table2, @I$7..@II$7)))::$4=vsum($2..$3);f2
#+Name: Final2
| Name | Table1 | Table2 | 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: $2=remote(Table1,@@#$7)::$3=remote(Table2,@@#$7)::$4=vsum($2..$3);f2
Or maybe safer
#+Name: Final3
| Name | Table1 | Table2 | 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: $4=vsum($2..$3);f2::@2$2..@>$2=remote(table1,@@#$7)::@2$3..@>$3=remote(table2,@@#$7)
To cite Bruno
"‘@#’ is substituted with the row number of the field where the current result will go to".
| Name | Table1 | Table2 | 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: $4=vsum($2..$3);f2::@2$2..@>$2=remote(table1,@@#$7)::@2$3..@>$3=remote(table2,@@#$7)
With this formula, your remote reference targets the field, that is in the same row number as the computed field in final.
SolutionRemyGauthier.ods
There is no one to one correspondence between the org
file and the ods
file.
I will just copy paste what I have gathered from the files, and I leave it to you to fill in the gaps ... gaps in my own response and the examples you have circulated.
Table1
Table2
Final
Final2
Final3
(("Final" .
;; #+TBLFM: $2='(org-lookup-first $1 '(remote(Table1, @I$1..@II$1)) '(remote(Table1, @I$7..@II$7)))
;; =SUMIF($''.$A$4:$''.$A$10,CONCATENATE("=",$B4),$''.$G$4:$''.$G$10)
"of:=SUMIF([$Table1.$A$4:$Table1.$A$10];CONCATENATE(\"=\";[.$B4]);[$Table1.$G$4:$Table1.$G$10])")
("Final (alternate)" .
;; #+TBLFM: $2=remote(Table1,@@#$7)
;; =SUMIF(OFFSET(INDEX(Table1,1,1),0,0,ROWS(Table1),1),CONCATENATE("=",$B4),OFFSET(INDEX(Table1,1,7),0,0,ROWS(Table1),1))
"of:=SUMIF(OFFSET(INDEX(Table1;1;1);0;0;ROWS(Table1);1);CONCATENATE(\"=\";[.$B4]);OFFSET(INDEX(Table1;1;7);0;0;ROWS(Table1);1))")
("with-vslookup" .
;; This table doesn't exist in org snippet
;; =VLOOKUP(B4,Table1.$A$4:$G$10,7,FALSE())
"of:=VLOOKUP([.B4];[Table1.$A$4:.$G$10];7;FALSE())")
("static" .
;; =$Table1.G5
"of:=[$Table1.G5]"))
There cannot be a one-to-one correspondence. The ods file contained 4 solutions
The org file contained three solutions
$2=remote
@2$2..@>$2=remote
So I decided to reduce the ods example to two solutions, using vslookup and the static link and the org example I reduce also to two
org-lookup-first
as a substitute for vslookup andSo here is the org example
#+Name: Table1
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS |
|----------+-----+-----+-----+-----+-----+------|
| 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: $7=vsum($2..$6);f2
#+Name: Table2
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS |
|----------+-----+-----+-----+-----+-----+------|
| 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: $7=vsum($2..$6);f2
#+Name: Final1
| Name | Table1 | Table2 | 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: $2='(org-lookup-first $1 '(remote(Table1, @I$1..@II$1)) '(remote(Table1, @I$7..@II$7)))::$3='(org-lookup-first $1 '(remote(Table2, @I$1..@II$1)) '(remote(Table2, @I$7..@II$7)))::$4=vsum($2..$3);f2
#+Name: Final2
| Name | Table1 | Table2 | 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: $2=remote(Table1,@@#$7)::$3=remote(Table2,@@#$7)::$4=vsum($2..$3);f2
Thanks. Looks good.
Notes to myself ...
(("Final1" . "of:=VLOOKUP([.A2];[Table1.$A$1:.$G$7];7;FALSE())")
("Final2" . "of:=[$Table1.G2]"))
#+name: Final1
| Name | Table1 | Table2 | Total |
| Smith | =VLOOKUP(A2,Table1.$A$1:$G$7,7,FALSE()) | =VLOOKUP(A2,Table2.$A$1:$G$7,7,FALSE()) | =SUM(B2:C2) |
#+name: Final2
| Name | Table1 | Table2 | Result |
| Smith | =$Table1.G2 | =$Table2.G2 | =SUM(B2:C2) |
#+name: Table1
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS |
| Smith | 2 | 3 | 4 | 6 | 7 | =SUM(B2:F2) |
#+name: Table2
| Name | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResS |
| Smith | 1 | 3 | 5 | 8 | 9 | =SUM(B2:F2) |
@ouboub, this space is intentionally left blank so that you could fill it in.
Avoid stuff like
identity
(the `Org' info doesn't mention it) which are concoctions known only to the original author or those who were conversing with him when he was active.We shouldn't focus on what we do NOT understand or need.
Feel free to fill in the details. I will handle this bug, once the
remote()
is handled.