2067 Views 8 Replies Latest reply: Mar 25, 2010 9:20 AM by KOENIG Yvan
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:47
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.
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:18
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.
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.
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!
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:43