Hi Alan,

seems what you're looking for is some relational database functionality, which
is not possible with org spreadsheets :-(

Find a possible solution for your problem in the attached org file.  You will
need org-babel to execute the source blocks.

hth,
	Stephan

Also sprach Alan Boudreault:
> Hi Sébastien and thank you for your answer.
> 
> The problem with that technique is that I'll have to name ALL the cell
> for ALL the colomn. Here's an example of my table:
> 
> #+TBLNAME: FOOD
>  | Name            | Brand         | Amounts (g) | Calories | Protein |
> Carbohydrate |  Fat |
>  
> |-----------------+---------------+-------------+----------+---------+--------------+------|
> 
>  | Poitrine Poulet     | KirkLand      |         100 |      100 |     
> 24 |            0 |    1 |
>  | Capeli d'angelo    | Primo         |          85 |      300 |      10
> |           63 |    1 |
>  | Couscous              | Clic          |          62 |      220
> |       8 |           46 |    1 |
>  | Fromage Cottage | Sealtest      |         125 |      100 |      15
> |            8 |    1 |
>  | Ficello                   | Black Diamond |          21 |       60
> |       6 |            0 |    4 |
>  | Concombre cru    |               |         100 |       15 |    0.65
> |         3.63 | 0.11 |
>  | Clementine cru    |               |         100 |       47 |    0.85
> |        12.02 | 0.15 |
> 
> In the table 2, I would like to be able to just set a column to ie. 2
> (for "Poitrine Poulet"), then I'll be able to get all the  info of the
> different columns I need to do my calculs. I see there is also a "_"
> option for row name, but I'm unsure on how to specify the column desired
> with that name and how to get it evaluated if I need to concat something...
> 
> regards,
> Alan
> 
> Sébastien Vauban wrote:
>> Hi Alan,
>>
>> Alan Boudreault wrote:
>>  
>>> Here's what I'm trying to do (without success) in my spreadsheet:
>>>
>>> I have a first table :
>>>
>>> #+TBLNAME: table1
>>> | ... |... |.... |...|...
>>>
>>> and a second table:
>>>
>>> #+TBLNAME: table2
>>> | line_reference | ... |... |.... |...|...
>>>
>>> The line_reference is the line number to get some information in the
>>> table1.
>>> So in a cell of the table2, I tried a formula like:
>>> remote(table1,@$1$3) ($1
>>> = the line_reference) which of course, didn't work.
>>>
>>> then I tried to use elisp: remote(table1,'(concat "@" $1 "$3")') ....
>>> but no
>>> success anyway.
>>>
>>> after all, I decided to merge my 2 tables because it looks like I can
>>> use
>>> elisp inside a org function (remote). The last tried formula was:
>>> '(concat
>>> "@" $1 "$3")' ,($1 = the line_reference). The problem of this formula is
>>> only that I got the string "@2$3" inside the cell and it's not
>>> evaluated as
>>> a formula.
>>>
>>> Could anyone give me some hint to get done what I'm trying to do?
>>>     
>>
>> What I do, which works:
>>
>> - give a name to the cell in table1
>> - reference it (in table2) by its name
>>
>>
>>   #+TBLNAME: Eau
>>   |   | Date             | Relevé m3 | Consommation | Prix TVAC |
>>   |---+------------------+-----------+--------------+-----------|
>>   |   | [2009-11-15 Sun] | 2072.6263 |       0.0000 |      0.00 |
>>   |   | [2009-11-22 Sun] | 2074.1774 |       1.5511 |      1.55 |
>>   |   | [2009-11-29 Sun] | 2076.0834 |       1.9060 |      1.91 |
>>   |   | [2009-12-06 Sun] | 2077.6757 |       1.5923 |      1.59 |
>>   |   | [2009-12-13 Sun] | 2079.2140 |       1.5383 |      1.54 |
>>   |   | [2009-12-20 Sun] | 2081.4222 |       2.2082 |      2.21 |
>>   |   | [2009-12-27 Sun] | 2083.2146 |       1.7924 |      1.79 |
>>   |   | [2010-01-03 Sun] | 2085.4916 |       2.2770 |      2.28 |
>>   |   | [2010-01-10 Sun] | 2087.4283 |       1.9367 |      1.94 |
>>   | ^ |                  |           |              |       Eau |
>>   #+TBLFM: $4=@+0$-1-@-1$-1;%.4f::$5=$4*1;%.2f::@2$4=0;%.4f
>>
>>
>>   | Énergie     | Prix TVAC |
>>   |-------------+-----------|
>>   | Eau         |      1.94 |
>>   | Gaz         |    156.81 |
>>   | Électricité |     11.64 |
>>   |-------------+-----------|
>>   | Total       |    170.39 |
>>   #+TBLFM:
>> @2$2=remote(Eau,$Eau)::@3$2=remote(Gaz,$Gaz)::@4$2=remote(Elec,$Elec)::@5$2=vsum(@-I..@-II);%.2f
>>
>>
>> Best regards,
>>   Seb
>>
>>   
> 
>