Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching Worksheets
Hi all,
I'm looking to modify my search of other worksheets. At the moment I am currently using this formula which tells me the number of times that A6 is in the "Second Worksheet" in Column K. =SUMPRODUCT(--ISNUMBER(SEARCH(A6,'Second Worksheet'!$K$3:$K$279))) Now i want to modify it to instead of returning the number of times that A6 is in Column K in the Second Worksheet, i want it to return the cell references that it occurs in. So instead of returning 3 it will return K70 K120 K200. I would even be happy if it just returned the row where it was located. Is this possible if so what functions should i be looking at? Cheers, Paddy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching Worksheets
Are you using Search because A6 is a substring of what might be in 'Second
Worksheet'!$K$3:$K$279? Biff wrote in message oups.com... Hi all, I'm looking to modify my search of other worksheets. At the moment I am currently using this formula which tells me the number of times that A6 is in the "Second Worksheet" in Column K. =SUMPRODUCT(--ISNUMBER(SEARCH(A6,'Second Worksheet'!$K$3:$K$279))) Now i want to modify it to instead of returning the number of times that A6 is in Column K in the Second Worksheet, i want it to return the cell references that it occurs in. So instead of returning 3 it will return K70 K120 K200. I would even be happy if it just returned the row where it was located. Is this possible if so what functions should i be looking at? Cheers, Paddy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching Worksheets
Try one of these. Both are array formulas and need to entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER): This first one is based on your current formula. I'm assuming that by using Search, A6 is a substring: =IF(ROWS($1:1)<=COUNTIF(K$3:K$279,"*"&A$6&"*"),"K" &SMALL(IF(ISNUMBER(SEARCH(A$6,K$3:K$279)),ROW(K$3: K$279)),ROWS($1:1)),"") If A6 is not a substring: =IF(ROWS($1:1)<=COUNTIF(K$3:K$279,A$6),"K"&SMALL(I F(K$3:K$279=A$6,ROW(K$3:K$279)),ROWS($1:1)),"") Copy down until you get blanks. If A6 is not a substring: =SUMPRODUCT(--ISNUMBER(SEARCH(A6,'Second Worksheet'!$K$3:$K$279))) You can just use: =COUNTIF(K$3:K$279,A$6) Or, you can still use Countif for substings (although I generally prefer the Sumproduct formula): =COUNTIF(K$3:K$279,"*"&A$6&"*") Biff "T. Valko" wrote in message ... Are you using Search because A6 is a substring of what might be in 'Second Worksheet'!$K$3:$K$279? Biff wrote in message oups.com... Hi all, I'm looking to modify my search of other worksheets. At the moment I am currently using this formula which tells me the number of times that A6 is in the "Second Worksheet" in Column K. =SUMPRODUCT(--ISNUMBER(SEARCH(A6,'Second Worksheet'!$K$3:$K$279))) Now i want to modify it to instead of returning the number of times that A6 is in Column K in the Second Worksheet, i want it to return the cell references that it occurs in. So instead of returning 3 it will return K70 K120 K200. I would even be happy if it just returned the row where it was located. Is this possible if so what functions should i be looking at? Cheers, Paddy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching Across Worksheets: Please help! | New Users to Excel | |||
vba searching for strings in multiple worksheets | Excel Discussion (Misc queries) | |||
Define list of worksheets | Excel Worksheet Functions | |||
Merge Worksheets | Excel Discussion (Misc queries) | |||
Merge Worksheets | Excel Worksheet Functions |