8 Replies Latest reply: Mar 25, 2010 9:20 AM by KOENIG Yvan
SKYTORT Level 1 Level 1 (0 points)
I am busy setting up a budget/financial overview spreadsheet, and have formulated the cells to show spending by category and account type. There are 12 columns (for obvious reasons), however, I would like to create one pie chart that I could simply view depending on the month and year.

What I am trying to achieve is to have one pie chart which will give me a break down of the spending of each category by month. In other words, if I select "January" the pie chart must automatically adjust to display all category spending for January, and so on.

My columns are already formulated to display the information by month, but I am not particularly keen on having 12 pie charts floating around on the screen.

I know one can do so on MS Excel, but please let there be a way in Numbers.

Thanks a lot!

Macbook Pro, Mac OS X (10.5.8)
  • Level 8 Level 8 (41,790 points)


    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
  • SKYTORT Level 1 Level 1 (0 points)
    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.
  • Level 8 Level 8 (41,790 points)
    My English is far from perfect and I feel unable to explain a different way.

    Go to my idisk :
    <http://public.me.com/koenigyvan>
    and download :
    For_iWork:iWork '09:forNumbers09:autochart.zip
    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
  • SKYTORT Level 1 Level 1 (0 points)
    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!
  • Badunit Level 6 Level 6 (11,400 points)
    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.
  • Level 8 Level 8 (41,790 points)
    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:06
  • SKYTORT Level 1 Level 1 (0 points)
    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!
  • Level 8 Level 8 (41,790 points)
    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