megnin
Active Member
- Joined
- Feb 27, 2002
- Messages
- 340
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:
Here is the piece of concern from the above formula:
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.)
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!$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.)
