Search for record with specific name in a cell

I'm trying to add another criteria that will look at the cell in column Z to
see if the record has been tagged for use in report(s). Column Z can contain
one or more tags. Multiple tags would indicate that the record is to be
included in more than one report.

Cells in Column Z could look as follows; the letter/report names indicate in
which report(s) the record should be included.

AB, CDE, EFG, GHI, IJK, KLMN
AB, CDE, GHI, KLMN
AB, CDE, EFG, GHI, IJK, KLMN
EFG, GHI, IJK, KLMN
AB
AB, KLMN

I want to retrieve the records that meet the criteria to be added to report
AB, CDE, etc. I'm using the following query and want to add the report
criteria to this:

=SUMPRODUCT((MONTH('[Wkly Rpt 2005.xls]Data'!$A$2:$A$500)=MONTH(S$1))*
((LEFT('[Wkly Rpt 2005.xls]Data'!$B$2:$B$500,3)="STP"))*
((LEFT('[Wkly Rpt 2005.xls]Data'!$T$2:$T$500,1)=$K$2)))

Try...

=SUMPRODUCT(--('[Wkly Rpt
2005.xls]Data'!$A$2:$A$500<>""),--(MONTH('[Wkly Rpt
2005.xls]Data'!$A$2:$A$500)=MONTH(S$1)),--(LEFT('[Wkly Rpt
2005.xls]Data'!$B$2:$B$500,3)="STP"),--(LEFT('[Wkly Rpt
2005.xls]Data'!$T$2:$T$500,1)=$K$2),--(ISNUMBER(SEARCH("AB",'[Wkly Rpt
2005.xls]Data'!$Z$2:$Z$500))))

If you want the month and year to be met as a criteria, try...

=SUMPRODUCT(--('[Wkly Rpt 2005.xls]Data'!$A$2:$A$500-DAY('[Wkly Rpt
2005.xls]Data'!$A$2:$A$500)+1=DATE(YEAR(S$1),MONTH(S$1),1)),--(LEFT('[Wkl
y Rpt 2005.xls]Data'!$B$2:$B$500,3)="STP"),--(LEFT('[Wkly Rpt
2005.xls]Data'!$T$2:$T$500,1)=$K$2),--(ISNUMBER(SEARCH("AB",'[Wkly Rpt
2005.xls]Data'!$Z$2:$Z$500))))

Hope this helps!

In article <B36EBC29-C04B-4A58-85E4-0C4BBDB6B756@microsoft.com>,
"pomalley" <pomalley@discussions.microsoft.com> wrote:

> I'm trying to add another criteria that will look at the cell in column Z to
> see if the record has been tagged for use in report(s). Column Z can contain
> one or more tags. Multiple tags would indicate that the record is to be
> included in more than one report.
>
> Cells in Column Z could look as follows; the letter/report names indicate in
> which report(s) the record should be included.
>
> AB, CDE, EFG, GHI, IJK, KLMN
> AB, CDE, GHI, KLMN
> AB, CDE, EFG, GHI, IJK, KLMN
> EFG, GHI, IJK, KLMN
> AB
> AB, KLMN
>
> I want to retrieve the records that meet the criteria to be added to report
> AB, CDE, etc. I'm using the following query and want to add the report
> criteria to this:
>
> =SUMPRODUCT((MONTH('[Wkly Rpt 2005.xls]Data'!$A$2:$A$500)=MONTH(S$1))*
> ((LEFT('[Wkly Rpt 2005.xls]Data'!$B$2:$B$500,3)="STP"))*
> ((LEFT('[Wkly Rpt 2005.xls]Data'!$T$2:$T$500,1)=$K$2)))

Thanks very much. It works like a charm. You're terrific.

"pomalley" wrote:

> I'm trying to add another criteria that will look at the cell in column Z to
> see if the record has been tagged for use in report(s). Column Z can contain
> one or more tags. Multiple tags would indicate that the record is to be
> included in more than one report.
>
> Cells in Column Z could look as follows; the letter/report names indicate in
> which report(s) the record should be included.
>
> AB, CDE, EFG, GHI, IJK, KLMN
> AB, CDE, GHI, KLMN
> AB, CDE, EFG, GHI, IJK, KLMN
> EFG, GHI, IJK, KLMN
> AB
> AB, KLMN
>
> I want to retrieve the records that meet the criteria to be added to report
> AB, CDE, etc. I'm using the following query and want to add the report
> criteria to this:
>
> =SUMPRODUCT((MONTH('[Wkly Rpt 2005.xls]Data'!$A$2:$A$500)=MONTH(S$1))*
> ((LEFT('[Wkly Rpt 2005.xls]Data'!$B$2:$B$500,3)="STP"))*
> ((LEFT('[Wkly Rpt 2005.xls]Data'!$T$2:$T$500,1)=$K$2)))
>