Need a formula to 'find' word in cells of column from a long l
Let XL create the proper path for you!
Enter an equal sign in a cell of the current WB, then navigate to the other
sheet in question, and click in a cell there, and hit <Enter.
This creates a link to that sheet, from cell to cell, but, more importantly
to you, you can see the exact path in that link formula.
Close the WB, and you'll see the formula expand to show you the *entire*
link.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Deden" wrote in message
...
Thanks. this helps. Now I need to find the syntax instructions to refer
to
another file and worksheet for the a1:a5 array. I can't find any help in
Excel 2007. I remember the worksheet name must be followed by an ! . I
can't remember the character for the workbook! There's got to be an
easy
way to look up these details.
Thanks so much for your prompt answer.
Deden
"T. Valko" wrote:
One way...
Assume the list of names to search for is in the range A1:A5 - sam alice
helen jim john
Enter this array formula** in Z1 and copy down as needed:
=IF(COUNT(SEARCH(A$1:A$5,B1)),"Yes","")
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
"Deden" wrote in message
...
In a spreadsheet of mostly textual information, one column consists of
multiple names. I need to identify matches against a list of about
300
names.
A less complex example:
The list of names to look for: sam alice helen jim john
Column cells
B1 - kate mark julie thelma
B2 - mike helen anne anson - 1 hit on 'helen'
B3 - jerry kathy sally sam - 1 hit on 'sam'
I need a mechanism to look for names from the list and identify
matches by
adding a flag, 'yes', in another column, Z
Match or Find or Search, seem to only identify a single specified
string.
A
workaround would be to construct the formula and run it individually
for
each
name. Is that my only option? Which function would be best?
Thanks.
|