This discussion is archived
2040 Views 8 Replies Latest reply: Mar 25, 2010 9:20 AM by KOENIG Yvan
Currently Being ModeratedMar 17, 2010 10:58 AM (in response to SKYTORT)
In the table entitled "charter", the cell A1 contains a popup menu allowing us to choose a month to chart.
In cells A2 thru A11, the formula is :
=INDIRECT (ADDRESS(ROW(),1,,,"Tableau 1"))
In cells B2 thru B11, the formula is :
=HLOOKUP($A$1,Tableau 1 :: $B:$K,ROW())
The chart is built from cells A2:B11
Yvan KOENIG (VALLAURIS, France) mercredi 17 mars 2010 18:58:47To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.4.11)
Currently Being ModeratedMar 17, 2010 10:42 PM (in response to KOENIG Yvan)KOENIG,
Thanks for the response, however, I can't make sense of it. I tried fooling around with it but the logic is a little trickier than I expected.
What you have done is exactly what I want. I even copied and pasted your indirect function and renamed the table references to match mine, but it does not work. I can't even get the basic indirect to work!
Can you retry explaining as you would to someone who can barely count to 100!
Thanks for the help. I hope we can resolve this issue.Macbook Pro, Mac OS X (10.5.8)
Currently Being ModeratedMar 18, 2010 12:47 AM (in response to SKYTORT)My English is far from perfect and I feel unable to explain a different way.
Go to my idisk :
and download :
Double click to expand the archive.
You will get the document ready to use.
The only problem which I may think of is the formula used in cells A2:A11 of the table 'charted'.
If we rename the main table (Tableau 1), the string which is usd as a reference to this table doesn't update automatically.
I already reported that as a huge bug but I have no feedback about that.
Yvan KOENIG (VALLAURIS, France) jeudi 18 mars 2010 08:47:18To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.4.11)
Currently Being ModeratedMar 18, 2010 10:59 PM (in response to KOENIG Yvan)KOENIG,
Thnks for your efforts to help me, but the INDIRECT function just makes NO sense to whatsoever. I downloaded your file and just substituted my own values, but it still does not want to reference to the correct chart.
Once I figure out how to paste screen shots on here I will paste my actual work sheets and someone can try and help my write the appropriate function to get what I am looking for.
I look forward to hearing from you. You definitely know what I am looking for, I just seems to be messing up something somewhere, part of as what I said already, my lack of knowledge/understanding of the INDIRECT function.
Regards!Macbook Pro, Mac OS X (10.5.8)
Currently Being ModeratedMar 19, 2010 12:37 AM (in response to SKYTORT)Is it possible that your localization uses semicolons instead of commas in formulas? That might explain why the copy/paste of Yvan's formulas does not work for you.
Yvan is using the INDIRECT function because it is unaffected by any sorting you might do to your data table.
=INDIRECT("Table 1::A1") is a reference to Table 1::A1. It is similar to the more simple function =Table 1::A1 except it will ALWAYS point to Table 1::A1, even if Table 1 is re-sorted. Note that capitalization is important in the table name; the name must be exactly the same.
ADDRESS(row, column, addr-type, addr-style, table) will create a string such as "Table 1::A1" based on the numbers and strings you use in the function.Mac Pro 2008, Mac OS X (10.5.6)
Currently Being ModeratedMar 19, 2010 11:20 AM (in response to SKYTORT)Did you download the file which I inserted on my iDisk ?
If you do that, you will get a file which will work with no changes of yours.
I may try to help but I can't do the work for you !
Yvan KOENIG (VALLAURIS, France) vendredi 19 mars 2010 19:20:06To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.4.11)
Currently Being ModeratedMar 25, 2010 9:00 AM (in response to KOENIG Yvan)KOENIG,
Thanks, I finally wrapped my head around this indirect and address functions you have used.
I must say I am rather disappointed in the route I had to follow with numbers in order to get the desired results. Excel is somewhat easier in this regard, but since I am trying hard to stay away from Windows, I will leave it at that!
Thanks again for the help!Macbook Pro, Mac OS X (10.5.8)
Currently Being ModeratedMar 25, 2010 9:20 AM (in response to SKYTORT)I don't know why Apple engineers built Numbers this way but I assume that they had good reasons to do that.
Excel completely dereference the stored values, Numbers doesn't. This is why we must do the trick ourself.
In my response, I used INDIRECT(ADDRESS(… to be able to sort the main table (Tableau 1) if required.
If it's not required, you may replace the formula in cell A2 of charter by the simple :
=Tableau 1 :: A
then fill down from A2 to A11.
Yvan KOENIG (VALLAURIS, France) jeudi 25 mars 2010 17:19:43To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.4.11)