Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for record with specific name in a cell
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 , "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))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for record with specific name in a cell
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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete cell that doesn't contain a specific word | Excel Discussion (Misc queries) | |||
Specific cell values | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
How can I search for more than one text character in a cell? | Excel Worksheet Functions | |||
How do I search for more than one text character in one cell? | Excel Worksheet Functions |