Data Validation - Can 2 items from a list be combined?

[RShaw]

Excel 2000

I use numerous SUMPRODUCT formulas that generate results based on an item
selected from a validation list. These items are "areas" within a
"division". The worksheet from which the formulas draw the data is organized
by "area". What I need to accomplish is to provide results based on a
"division", which is made up of 2 or more "areas". My preference is to find
a solution that recognizes each "area" and subsequently, that a specific
"division" = "area 1" + "area 3" + "area 6", for example. Modifying the
formulas will prove very onerous and may prove impossible, due to the length
of many of the formulas. Changing the formulas will also likely mean
changing the data from which the formulas draw, which defeats the purpose.
Can named ranges be used? If so, how can I use the named ranges to achieve
this result? I have attempted the named range approach, but have been
unsuccessful - I'm likely doing something wrong if that's where the solution
lies.

Thanks!

 

[Gary Brown]

Can you use a pivot table to meet your needs?

 

[RShaw]

Thank-you for replying Gary.

The SUMPRODUCT formulas search for specific text that indicates the status
of each "record". So with my understanding of pivot tables, only counts are
provided and so this detail cannot be captured. For example, one column can
have "Yes", "No" and one or two other possible descriptors that provide
information on status. The formulas count each of those possible outcomes.
Unless there is a way around that limitation with pivot tables, my sense is
that the pivot table can't accomplish the detail I require. Please let me
know if you agree. I've been experimenting with list boxes, although
admittedly I don't have a lot of experience in using them . . .