Thread: Need Validation type drop-down wildcard in Sumproduct

I have a worksheet report that counts question answers filtered by facility, date range and case manager. I use Data Validation "List" criteria to make the in-cell drop-down lists for the criteria of the variable values. For most of them the selected value works fine, but for the case manager drop-down I need a value in the list, other than the peoples names, to represent "All" or be a wildcard and include all values in the selection.

Here is the formula:

Code:

=SUMPRODUCT(--(WTP!$P$2:$P$420='WTP Report'!F$4),--(WTP!$I$2:$I$420='WTP Report'!$I$1),--(WTP!$M$2:$M$420='WTP Report'!$M$2),--(OFFSET(WTP!$A$2:$A$420,0,MATCH($Q6,WTP!$1:$1,FALSE)-1)=E$5)*1)

Here is the piece of concern from the above formula:

Code:

(WTP!$M$2:$M$420='WTP Report'!$M$2)

WTP Report!$M$2 is where the drop-down list is located. It gets it's list from a range containing a bunch of peoples names. Say, $AA$2:$AA$32
WTP!$M$2:$M$420 is where the names from the records in the database are stored.

I'd like to be able to stick some value like % or * or "%*%" in the list of names that would act like a "All" wildcard. I tried those, they don't work. ;-)

Thanks in advance.
(Please don't just inform me that I can't use a wildcard in a sumproduct, I'm looking for a solution to a problem.)

Hard to make sure it is right with no sheet to test, but try replacing that part with:

--ISNUMBER(MATCH(WTP!$M$2:$M$420,IF('WTP Report'!$M$2="*",WTP!$AA$2:$AA$32,'WTP Report'!$M$2),0))

Where you can place a "*" range WTP!$AA$2:$AA$32 so that it is included in drop down..

How about:
--(WTP!$M$2:$M$420='WTP Report'!$M$2)
Becomes
((WTP!$M$2:$M$420='WTP Report'!$M$2)+1*('WTP Report'!$M$2="all"))
?

Like NBVC, tested out of context, but seemed to work. Will return peculiar results if M contains "all" as an entry somewhere

CC

Originally Posted by megnin

Here is the one I used:

=SUMPRODUCT(--(WTP!$P$2:$P$420='WTP Report'!F$4),--(WTP!$I$2:$I$420='WTP Report'!$I$1),--(('WTP Report'!$M$2="All)+(WTP!$M$2:$M$420='WTP Report'!$M$2)>0),--(OFFSET(WTP!$A$2:$A$420,0,MATCH($Q6,WTP!$1:$1,FALSE)-1)=E$5)*1)

I then just added "All" to the top of the list of names.

Thank you. Several of those techniques looked useful.

Gotten from here:

http://www.mrexcel.com/forum/showthread.php?t=420494

Which is a cross-post... breaking another of our rules...