Conditional Formatting of Charts

Question

Level 1

0 points

Conditional Formatting of Charts

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)

Posted on Mar 17, 2010 10:22 AM

Reply

Answer 1

Best reply

KOENIG Yvan

Level 8

41,807 points

Mar 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:47

Reply

Answer 2

SKYTORT Author

Level 1

0 points

Mar 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.

Reply

Answer 3

KOENIG Yvan

Level 8

41,807 points

Mar 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 :
<http://public.me.com/koenigyvan>
and download :
For_iWork:iWork '09:for Numbers09: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

Reply

Answer 4

SKYTORT Author

Level 1

0 points

Mar 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!

Reply

Answer 5

Badunit

Level 7

26,080 points

Mar 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.

Reply

Answer 6

KOENIG Yvan

Level 8

41,807 points

Mar 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:06

Reply

Answer 7

SKYTORT Author

Level 1

0 points

Mar 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!

Reply

Answer 8

KOENIG Yvan

Level 8

41,807 points

Mar 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:43

Reply