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?


--
paris3
------------------------------------------------------------------------
paris3's Profile: http://www.excelforum.com/member.php...fo&userid=3542
View this thread: http://www.excelforum.com/showthread...hreadid=382164