Dynamic bar chart, must ignore "n/a" data...

[paris3]

Okay, gang, here's what I am trying to do. Assume I cannot use macros
or pivot tables (the end chart must be usable by folks with ZERO excel
ability.) Formulas only. (I know this would be easy with pivot
tables.)

Assume I have a table that is calculating how many pieces of fruit I
pick. I have a column (A) that is called FRUIT. The user of the
spreadsheet picks the type of fruit from a validated drop down list in
(A) and then enters the number of pieces picked in (B). so the data
might look like this:

APPLE 2
APPLE 3
PEAR 2
PEACH 2
APPLE 4

Where each row is a different day's picking.

I am using a dynamic named range, located off to the right side of the
sheet, so if the user wants to add a new type of fruit, he just types
it into the list and it then shows up in the drop down from now on.

Because some times the fruit entry isn't applicable, one of the
drop-down entries is NOT APPLICABLE.

What I want to do is create a bar chart (has to be a bar chart) that
displays the TOTALS for each type of fruit, but which ignores the
entries marked NOT APPLICABLE. That would be easy, by just doing
SUMIF's. Using the example above, I would have a bar chart that showed
me:

APPLES 9
PEAR 2
PEACH 2

But because the user may add additional types of fruit at any given
time, how to i make the bar chart dynamic, showing the sums of new
fruit while continuing to ignore the NOT APPLICABLE entries? I need
the chart to dynamically update, without calculating the NOT
APPLICABLEs.

All the user is doing is entering the data, not fiddling with the
chart.

Any ideas?

 

[Domenic]

Assuming that Column A contains the type of fruit, Column B contains th
number of fruits picked, and that the first row contains you
headers/labels, try the following...

C1: enter a 0 (zero)

C2, copied down:

=IF((A2<>"")*(B2<>"")*(A2<>"No
Applicable")*(ISNA(MATCH(A2,$A$1:A1,0))),LOOKUP(9.99999999999999E+307,$C$1:C1)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,C:C)

E2, copied down:

=IF(ROW()-ROW($E$2)+1<=$D$1,MATCH(ROW()-ROW(E$2)+1,C:C,0),"")

F1: enter Fruit (just a label)

G1: enter Total Picked (just a label)

F2, copied down:

=IF(N($E2),INDEX(A:A,$E2),"")

G2, copied down:

=IF(N($E2),SUMIF(A:A,F2,B:B),"")

Define the following ranges...

Insert > Name > Define

Name: Fruit

Refers to:
=Sheet1!$F$2:INDEX(Sheet1!$F$2:$F$65536,MATCH(9.99999999999999E+307,Sheet1!$G$2:$G$65536))

Click Add

Name: Total_Picked

Refers to:
=Sheet1!$G$2:INDEX(Sheet1!$G$2:$G$65536,MATCH(9.99999999999999E+307,Sheet1!$G$2:$G$65536))

Click Ok

Create your chart using Column F and Column G

Change the chart's Data Source by clicking on the column series t
select it

In the Formula Bar, change the cell referrences to the named ranges...

=SERIES(Sheet1!$G$1,Sheet1!Fruit,Sheet1!Total_Picked,1)

Change the references to all sheet names accordingly.

If you'd like, I can email you a sample file. If so, you can send m
your email address by Private Message.

Hope this helps!

 

[paris3]

thanks so much for the effort you put into this! I will give this a try
and report back

I won't need a sample file, your description looks easy enough to
follow.

Thanks !

 

[Tushar Mehta]

I know you know your task better than I. That said...

You are kidding, right?

Zero XL knowledge and they know how to update a list used for
validating data entry in a column, how to propagate cells with formulas
as new fruit types are added, how to use a drop-down for data entry,
and how to enter a number in the correct adjacent column.

If you know this is easy with a Pivottable, why not use one? After
all, if this is not a homework assignment, it is not rocket science to
tell someone "click anywhere in the PT, then click the refresh button
(the one with the exclamation mark) on the PT toolbar."

And, if they cannot figure how to click a button, you have bigger
organizational problems than automagically updated pretty pictures.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Refining Tushar's post, put a textbox in the sheet that says "Click
anywhere in the PT, then click the refresh button (the one with the
exclamation mark) on the PT toolbar." If this is insufficient for any
user, have their manager contact human resources.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______