Using a drop down box to select all results

[Alex Delamain]

I have a summary page which pulls data together from several sheets an
displays summary statistics etc.

I want to be able to use a drop down list to select particular product
and only show data relating to those products

- so far so good.

But ..... having put in my drop down and referenced it in my formulae
now cannot show the summary data for ALL products.

Is there a wildcard that can be used in drop downs to select all

 

[Debra Dalgleish]

You could add (All) to your list of products, and check for that
selection in the formula. For example:
=IF(A2="(All)",option1,option2)

There's a sample workbook here that uses data validation and an advanced
filter to extract a list of products:

http://www.contextures.com/excelfiles.html
Under the heading 'Filters', look for 'Product List by Category'

 

[Alex Delamain]

Sorry to be a pain but I am not sure how that works.

I am using the product name in a sumproduct formula along with othe
limiting conditions so it is not a simple case of either / or.

Is it possible to include a range name in the list that would the
refer to all the other products

 

[Debra Dalgleish]

You could use an IF in the Sumproduct formula, e.g.:

=SUMPRODUCT((IF($I$2="(All)",$A$2:$A$59<>"",$A$2:$A$59=$I$2))*($D$2:$D$59=$D$2)*($E$2:$E$59))

I don't understand your question about the range name.

 

[Alex Delamain]

The formula I am using is:

=IF(SUMPRODUCT((product=$C$2)*(datekey>=$B5)*(datekey<$B6))=0,#N/A,SUMPRODUCT(((product=$C$2)*(datekey>=$B5)*(datekey<$B6)*(mxsubs<>"")*(QCsubs<>"")*(mxsubs-QCsubs))/SUMPRODUCT((product=$C$2)*(datekey>=$B5)*(datekey<$B6)*(mxsubs<>"")*(QCsubs<>""))))

which is probably overcomplicated but was the best I could come u
with.
Basically it checks that there are results for the selected produc
between two dates. If there are and there are results for both mxsub
and qcsubs it subtracts one from the other. Finally it divides that su
by the count of entries which match the criteria to give an averag
error for the selected product and period.

Cell C2 holds the drop down list. I would like, if possible, to be abl
to use it to select a single product or all products

 

[Debra Dalgleish]

If you leave cell C2 blank, to indicate that all products should be
included, you could modify your formula to:

=IF($C$2="",SUMPRODUCT(((DateKey>=$B5)*(DateKey<$B6)*(mxsubs<>"")*(QCsubs<>"")*(mxsubs-QCsubs))
/SUMPRODUCT((DateKey>=$B5)*(DateKey<$B6)*(mxsubs<>"")*(QCsubs<>""))),
SUMPRODUCT(((product=$C$2)*(DateKey>=$B5)*(DateKey<$B6)*(mxsubs<>"")*(QCsubs<>"")*(mxsubs-QCsubs))
/SUMPRODUCT((product=$C$2)*(DateKey>=$B5)*(DateKey<$B6)*(mxsubs<>"")*(QCsubs<>""))))

 

[Alex Delamain]

Thanks - that will work as I can use blank or (All) and also put that i
the selection list