software

cjoint.com/data/bguHFjhv8r.htm

My problem is attached on the above link. Anyone can help? I have been
looking for solutions for long....to make a simple search engine.

Also, can I make Column C update automatically (show Found/NOT Found) when I
keep adding new TargetNames on Column B.

Appreciate any help from you!!
THANK YOU VERY MUCH!!!
First
Put this formula in cell C2 and copy it down as far as you need.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)gt;0,quot;FOUNDquot;,quot;NOT FOUNDquot;)

Second
Create your list of forbidden words in a blank column. For this example, I
started my list in Cell E2 on Sheet2.

Then, Insertgt;Namegt;Create
Name in Workbook: CheckList
Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
Click [OK]

That created a dynamic range name called CheckList that automatically
resizes to accomodate the number of items in the list beginning in cell E2 on
Sheet2.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;guyquot; wrote:

gt; cjoint.com/data/bguHFjhv8r.htm
gt;
gt; My problem is attached on the above link. Anyone can help? I have been
gt; looking for solutions for long....to make a simple search engine.
gt;
gt; Also, can I make Column C update automatically (show Found/NOT Found) when I
gt; keep adding new TargetNames on Column B.
gt;
gt; Appreciate any help from you!!
gt; THANK YOU VERY MUCH!!!
gt;
gt;
gt;

Minor correction...
The formula for C2 and below should be:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),quot;FOUNDquot;,quot;NOT FOUNDquot;)

(I wasn't paying good attention when I pasted the wrong version in my post)

***********
Regards,
Ron

XL2002, WinXP-Proquot;Ron Coderrequot; wrote:

gt; First
gt; Put this formula in cell C2 and copy it down as far as you need.
gt; =IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)gt;0,quot;FOUNDquot;,quot;NOT FOUNDquot;)
gt;
gt; Second
gt; Create your list of forbidden words in a blank column. For this example, I
gt; started my list in Cell E2 on Sheet2.
gt;
gt; Then, Insertgt;Namegt;Create
gt; Name in Workbook: CheckList
gt; Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
gt; Click [OK]
gt;
gt; That created a dynamic range name called CheckList that automatically
gt; resizes to accomodate the number of items in the list beginning in cell E2 on
gt; Sheet2.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;guyquot; wrote:
gt;
gt; gt; cjoint.com/data/bguHFjhv8r.htm
gt; gt;
gt; gt; My problem is attached on the above link. Anyone can help? I have been
gt; gt; looking for solutions for long....to make a simple search engine.
gt; gt;
gt; gt; Also, can I make Column C update automatically (show Found/NOT Found) when I
gt; gt; keep adding new TargetNames on Column B.
gt; gt;
gt; gt; Appreciate any help from you!!
gt; gt; THANK YOU VERY MUCH!!!
gt; gt;
gt; gt;
gt; gt;

It works!! Greatly appreciate your help!! THANKS A LOT!!
However, I still have confusions about SUMPRODUCT.
For the following two formulas,
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
it seems that they can produce the same result.
But I doubt how formula (A) works.
For example,
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied to
get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
muliplied first, then summed to get 1).
I want to know whether formula (A) can be reduced to Case 1 formula. If so,
the result will always be zero as long as there exists one unmatched word on
the checklist.
Sorry, I am really confused...I have searched a number of websites but none
of them supply me appropriate explanations...Can you explain this strange
thing? Please help!! Thanks!! (coz i not only want to solve the problem, but
also want to know how and why it is solved....to learn things.)

BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Many thanks again!!
(Really get a lot of prompt and constructive replies in this newsgroup.)

quot;Ron Coderrequot; gt; ...
gt; Minor correction...
gt; The formula for C2 and below should be:
gt; =IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),quot;FOUNDquot;,quot;NOT FOUNDquot;)
gt;
gt; (I wasn't paying good attention when I pasted the wrong version in my
gt; post)
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt;gt; First
gt;gt; Put this formula in cell C2 and copy it down as far as you need.
gt;gt; =IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)gt;0,quot;FOUNDquot;,quot;NOT FOUNDquot;)
gt;gt;
gt;gt; Second
gt;gt; Create your list of forbidden words in a blank column. For this example,
gt;gt; I
gt;gt; started my list in Cell E2 on Sheet2.
gt;gt;
gt;gt; Then, Insertgt;Namegt;Create
gt;gt; Name in Workbook: CheckList
gt;gt; Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
gt;gt; Click [OK]
gt;gt;
gt;gt; That created a dynamic range name called CheckList that automatically
gt;gt; resizes to accomodate the number of items in the list beginning in cell
gt;gt; E2 on
gt;gt; Sheet2.
gt;gt;
gt;gt; Does that help?
gt;gt;
gt;gt; ***********
gt;gt; Regards,
gt;gt; Ron
gt;gt;
gt;gt; XL2002, WinXP-Pro
gt;gt;
gt;gt;
gt;gt; quot;guyquot; wrote:
gt;gt;
gt;gt; gt; cjoint.com/data/bguHFjhv8r.htm
gt;gt; gt;
gt;gt; gt; My problem is attached on the above link. Anyone can help? I have been
gt;gt; gt; looking for solutions for long....to make a simple search engine.
gt;gt; gt;
gt;gt; gt; Also, can I make Column C update automatically (show Found/NOT Found)
gt;gt; gt; when I
gt;gt; gt; keep adding new TargetNames on Column B.
gt;gt; gt;
gt;gt; gt; Appreciate any help from you!!
gt;gt; gt; THANK YOU VERY MUCH!!!
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
Revised:

It works!! Greatly appreciate your help!! THANKS A LOT!!
However, I still have confusions about SUMPRODUCT.
For the following two formulas,
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
(B) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)gt;0
it seems that they can produce the same result.
But I doubt how formula (A) and (B) work.
For example,
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied to
get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
muliplied first, then summed to get 1).
I want to know whether formula (A) can be reduced to Case 1 formula. If so,
the result will always be zero as long as there exists one unmatched word on
the checklist.
Sorry, I am really confused...I have searched a number of websites but none
of them supply me appropriate explanations...Can you explain this strange
thing? Please help!! Thanks!! (coz i not only want to solve the problem, but
also want to know how and why it is solved....to learn things.)

BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Many thanks again!!
(Really get a lot of prompt and constructive replies in this newsgroup.)

quot;guyquot; gt; bl...
gt; It works!! Greatly appreciate your help!! THANKS A LOT!!
gt; However, I still have confusions about SUMPRODUCT.
gt; For the following two formulas,
gt; (A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
gt; it seems that they can produce the same result.
gt; But I doubt how formula (A) works.
gt; For example,
gt; Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied
gt; to get 0),
gt; Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
gt; muliplied first, then summed to get 1).
gt; I want to know whether formula (A) can be reduced to Case 1 formula. If
gt; so, the result will always be zero as long as there exists one unmatched
gt; word on the checklist.
gt; Sorry, I am really confused...I have searched a number of websites but
gt; none of them supply me appropriate explanations...Can you explain this
gt; strange thing? Please help!! Thanks!! (coz i not only want to solve the
gt; problem, but also want to know how and why it is solved....to learn
gt; things.)
gt;
gt; BTW, for the dynamic range checklist, should the formula be
gt; =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
gt; when there is a heading on top of the checklist?
gt;
gt; Many thanks again!!
gt; (Really get a lot of prompt and constructive replies in this newsgroup.)
gt;
gt; quot;Ron Coderrequot; gt; ...
gt;gt; Minor correction...
gt;gt; The formula for C2 and below should be:
gt;gt; =IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),quot;FOUNDquot;,quot;NOT FOUNDquot;)
gt;gt;
gt;gt; (I wasn't paying good attention when I pasted the wrong version in my
gt;gt; post)
gt;gt;
gt;gt; ***********
gt;gt; Regards,
gt;gt; Ron
gt;gt;
gt;gt; XL2002, WinXP-Pro
gt;gt;
gt;gt;
gt;gt; quot;Ron Coderrequot; wrote:
gt;gt;
gt;gt;gt; First
gt;gt;gt; Put this formula in cell C2 and copy it down as far as you need.
gt;gt;gt; =IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)gt;0,quot;FOUNDquot;,quot;NOT
gt;gt;gt; FOUNDquot;)
gt;gt;gt;
gt;gt;gt; Second
gt;gt;gt; Create your list of forbidden words in a blank column. For this example,
gt;gt;gt; I
gt;gt;gt; started my list in Cell E2 on Sheet2.
gt;gt;gt;
gt;gt;gt; Then, Insertgt;Namegt;Create
gt;gt;gt; Name in Workbook: CheckList
gt;gt;gt; Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
gt;gt;gt; Click [OK]
gt;gt;gt;
gt;gt;gt; That created a dynamic range name called CheckList that automatically
gt;gt;gt; resizes to accomodate the number of items in the list beginning in cell
gt;gt;gt; E2 on
gt;gt;gt; Sheet2.
gt;gt;gt;
gt;gt;gt; Does that help?
gt;gt;gt;
gt;gt;gt; ***********
gt;gt;gt; Regards,
gt;gt;gt; Ron
gt;gt;gt;
gt;gt;gt; XL2002, WinXP-Pro
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; quot;guyquot; wrote:
gt;gt;gt;
gt;gt;gt; gt; cjoint.com/data/bguHFjhv8r.htm
gt;gt;gt; gt;
gt;gt;gt; gt; My problem is attached on the above link. Anyone can help? I have been
gt;gt;gt; gt; looking for solutions for long....to make a simple search engine.
gt;gt;gt; gt;
gt;gt;gt; gt; Also, can I make Column C update automatically (show Found/NOT Found)
gt;gt;gt; gt; when I
gt;gt;gt; gt; keep adding new TargetNames on Column B.
gt;gt;gt; gt;
gt;gt;gt; gt; Appreciate any help from you!!
gt;gt;gt; gt; THANK YOU VERY MUCH!!!
gt;gt;gt; gt;
gt;gt;gt; gt;
gt;gt;gt; gt;
gt;
gt;
Regarding:
gt; (A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
gt; (B) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)gt;0
gt; it seems that they can produce the same result.

You're correct...both formulas return the same values. I replaced formula(B)
with formula(A) because formula(A) is more efficient.

Regarding:
gt; Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied to
gt; get 0),
gt; Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
gt; muliplied first, then summed to get 1).

I'm not sure I understand the question. Case 1 doesn't multiply those values
(1,0,0,0)...it adds them up. Case 2 does multiply the two arrays
(1,0,0,0)*(1,0,0,0), which resolve to (1,0,0,0)....the same as Case 1. So,
you can see that multiplying by 1 and testing for gt;0 is unnecessary. Case 1
is the better formula.

And, finally:
gt; BTW, for the dynamic range checklist, should the formula be
gt; =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
gt; when there is a heading on top of the checklist?

Answer: Yes!

Does that answer your questions?

***********
Regards,
Ron

XL2002, WinXP-Proquot;guyquot; wrote:

gt; Revised:
gt;
gt; It works!! Greatly appreciate your help!! THANKS A LOT!!
gt; However, I still have confusions about SUMPRODUCT.
gt; For the following two formulas,
gt; (A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
gt; (B) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)gt;0
gt; it seems that they can produce the same result.
gt; But I doubt how formula (A) and (B) work.
gt; For example,
gt; Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied to
gt; get 0),
gt; Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
gt; muliplied first, then summed to get 1).
gt; I want to know whether formula (A) can be reduced to Case 1 formula. If so,
gt; the result will always be zero as long as there exists one unmatched word on
gt; the checklist.
gt; Sorry, I am really confused...I have searched a number of websites but none
gt; of them supply me appropriate explanations...Can you explain this strange
gt; thing? Please help!! Thanks!! (coz i not only want to solve the problem, but
gt; also want to know how and why it is solved....to learn things.)
gt;
gt; BTW, for the dynamic range checklist, should the formula be
gt; =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
gt; when there is a heading on top of the checklist?
gt;
gt; Many thanks again!!
gt; (Really get a lot of prompt and constructive replies in this newsgroup.)
gt;
gt; quot;guyquot; gt; ¼¶¼g©ó¶l¥ó·s»D:uqqmL44EGHA.1088@tk2msftn gp13.phx.gbl...
gt; gt; It works!! Greatly appreciate your help!! THANKS A LOT!!
gt; gt; However, I still have confusions about SUMPRODUCT.
gt; gt; For the following two formulas,
gt; gt; (A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
gt; gt; it seems that they can produce the same result.
gt; gt; But I doubt how formula (A) works.
gt; gt; For example,
gt; gt; Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied
gt; gt; to get 0),
gt; gt; Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
gt; gt; muliplied first, then summed to get 1).
gt; gt; I want to know whether formula (A) can be reduced to Case 1 formula. If
gt; gt; so, the result will always be zero as long as there exists one unmatched
gt; gt; word on the checklist.
gt; gt; Sorry, I am really confused...I have searched a number of websites but
gt; gt; none of them supply me appropriate explanations...Can you explain this
gt; gt; strange thing? Please help!! Thanks!! (coz i not only want to solve the
gt; gt; problem, but also want to know how and why it is solved....to learn
gt; gt; things.)
gt; gt;
gt; gt; BTW, for the dynamic range checklist, should the formula be
gt; gt; =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
gt; gt; when there is a heading on top of the checklist?
gt; gt;
gt; gt; Many thanks again!!
gt; gt; (Really get a lot of prompt and constructive replies in this newsgroup.)
gt; gt;
gt; gt; quot;Ron Coderrequot; gt; ...
gt; gt;gt; Minor correction...
gt; gt;gt; The formula for C2 and below should be:
gt; gt;gt; =IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),quot;FOUNDquot;,quot;NOT FOUNDquot;)
gt; gt;gt;
gt; gt;gt; (I wasn't paying good attention when I pasted the wrong version in my
gt; gt;gt; post)
gt; gt;gt;
gt; gt;gt; ***********
gt; gt;gt; Regards,
gt; gt;gt; Ron
gt; gt;gt;
gt; gt;gt; XL2002, WinXP-Pro
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Ron Coderrequot; wrote:
gt; gt;gt;
gt; gt;gt;gt; First
gt; gt;gt;gt; Put this formula in cell C2 and copy it down as far as you need.
gt; gt;gt;gt; =IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)gt;0,quot;FOUNDquot;,quot;NOT
gt; gt;gt;gt; FOUNDquot;)
gt; gt;gt;gt;
gt; gt;gt;gt; Second
gt; gt;gt;gt; Create your list of forbidden words in a blank column. For this example,
gt; gt;gt;gt; I
gt; gt;gt;gt; started my list in Cell E2 on Sheet2.
gt; gt;gt;gt;
gt; gt;gt;gt; Then, Insertgt;Namegt;Create
gt; gt;gt;gt; Name in Workbook: CheckList
gt; gt;gt;gt; Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
gt; gt;gt;gt; Click [OK]
gt; gt;gt;gt;
gt; gt;gt;gt; That created a dynamic range name called CheckList that automatically
gt; gt;gt;gt; resizes to accomodate the number of items in the list beginning in cell
gt; gt;gt;gt; E2 on
gt; gt;gt;gt; Sheet2.
gt; gt;gt;gt;
gt; gt;gt;gt; Does that help?
gt; gt;gt;gt;
gt; gt;gt;gt; ***********
gt; gt;gt;gt; Regards,
gt; gt;gt;gt; Ron
gt; gt;gt;gt;
gt; gt;gt;gt; XL2002, WinXP-Pro
gt; gt;gt;gt;
gt; gt;gt;gt;
gt; gt;gt;gt; quot;guyquot; wrote:
gt; gt;gt;gt;
gt; gt;gt;gt; gt; cjoint.com/data/bguHFjhv8r.htm
gt; gt;gt;gt; gt;
gt; gt;gt;gt; gt; My problem is attached on the above link. Anyone can help? I have been
gt; gt;gt;gt; gt; looking for solutions for long....to make a simple search engine.
gt; gt;gt;gt; gt;
gt; gt;gt;gt; gt; Also, can I make Column C update automatically (show Found/NOT Found)
gt; gt;gt;gt; gt; when I
gt; gt;gt;gt; gt; keep adding new TargetNames on Column B.
gt; gt;gt;gt; gt;
gt; gt;gt;gt; gt; Appreciate any help from you!!
gt; gt;gt;gt; gt; THANK YOU VERY MUCH!!!
gt; gt;gt;gt; gt;
gt; gt;gt;gt; gt;
gt; gt;gt;gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;


quot;Ron Coderrequot; gt; ...
gt; Regarding:
gt;gt; (A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
gt;gt; (B) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)gt;0
gt;gt; it seems that they can produce the same result.
gt;
gt; You're correct...both formulas return the same values. I replaced
gt; formula(B)
gt; with formula(A) because formula(A) is more efficient.
gt;
gt; Regarding:
gt;gt; Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied
gt;gt; to
gt;gt; get 0),
gt;gt; Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers
gt;gt; are
gt;gt; muliplied first, then summed to get 1).
gt;
gt; I'm not sure I understand the question. Case 1 doesn't multiply those
gt; values
gt; (1,0,0,0)...it adds them up.
..
.....YES, YOU ARE RIGHT. I MADE A MISTAKE HERE...

Case 2 does multiply the two arrays
gt; (1,0,0,0)*(1,0,0,0), which resolve to (1,0,0,0)....the same as Case 1. So,
gt; you can see that multiplying by 1 and testing for gt;0 is unnecessary. Case
gt; 1
gt; is the better formula.
gt;
gt; And, finally:
gt;gt; BTW, for the dynamic range checklist, should the formula be
gt;gt; =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
gt;gt; when there is a heading on top of the checklist?
gt;
gt; Answer: Yes!
gt;
gt; Does that answer your questions?

......YES, ALL IS CLEAR NOW. THANKS A LOT!!

gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;guyquot; wrote:
gt;
gt;gt; Revised:
gt;gt;
gt;gt; It works!! Greatly appreciate your help!! THANKS A LOT!!
gt;gt; However, I still have confusions about SUMPRODUCT.
gt;gt; For the following two formulas,
gt;gt; (A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
gt;gt; (B) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)gt;0
gt;gt; it seems that they can produce the same result.
gt;gt; But I doubt how formula (A) and (B) work.
gt;gt; For example,
gt;gt; Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied
gt;gt; to
gt;gt; get 0),
gt;gt; Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers
gt;gt; are
gt;gt; muliplied first, then summed to get 1).
gt;gt; I want to know whether formula (A) can be reduced to Case 1 formula. If
gt;gt; so,
gt;gt; the result will always be zero as long as there exists one unmatched word
gt;gt; on
gt;gt; the checklist.
gt;gt; Sorry, I am really confused...I have searched a number of websites but
gt;gt; none
gt;gt; of them supply me appropriate explanations...Can you explain this strange
gt;gt; thing? Please help!! Thanks!! (coz i not only want to solve the problem,
gt;gt; but
gt;gt; also want to know how and why it is solved....to learn things.)
gt;gt;
gt;gt; BTW, for the dynamic range checklist, should the formula be
gt;gt; =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
gt;gt; when there is a heading on top of the checklist?
gt;gt;
gt;gt; Many thanks again!!
gt;gt; (Really get a lot of prompt and constructive replies in this newsgroup.)
gt;gt;
gt;gt; quot;guyquot; gt; hx.gbl...
gt;gt;
gt;gt; gt; It works!! Greatly appreciate your help!! THANKS A LOT!!
gt;gt; gt; However, I still have confusions about SUMPRODUCT.
gt;gt; gt; For the following two formulas,
gt;gt; gt; (A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
gt;gt; gt; it seems that they can produce the same result.
gt;gt; gt; But I doubt how formula (A) works.
gt;gt; gt; For example,
gt;gt; gt; Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are
gt;gt; gt; multiplied
gt;gt; gt; to get 0),
gt;gt; gt; Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers
gt;gt; gt; are
gt;gt; gt; muliplied first, then summed to get 1).
gt;gt; gt; I want to know whether formula (A) can be reduced to Case 1 formula. If
gt;gt; gt; so, the result will always be zero as long as there exists one
gt;gt; gt; unmatched
gt;gt; gt; word on the checklist.
gt;gt; gt; Sorry, I am really confused...I have searched a number of websites but
gt;gt; gt; none of them supply me appropriate explanations...Can you explain this
gt;gt; gt; strange thing? Please help!! Thanks!! (coz i not only want to solve the
gt;gt; gt; problem, but also want to know how and why it is solved....to learn
gt;gt; gt; things.)
gt;gt; gt;
gt;gt; gt; BTW, for the dynamic range checklist, should the formula be
gt;gt; gt; =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
gt;gt; gt; when there is a heading on top of the checklist?
gt;gt; gt;
gt;gt; gt; Many thanks again!!
gt;gt; gt; (Really get a lot of prompt and constructive replies in this
gt;gt; gt; newsgroup.)
gt;gt; gt;
gt;gt; gt; quot;Ron Coderrequot; gt; ...
gt;gt; gt;
gt;gt; gt;gt; Minor correction...
gt;gt; gt;gt; The formula for C2 and below should be:
gt;gt; gt;gt; =IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),quot;FOUNDquot;,quot;NOT FOUNDquot;)
gt;gt; gt;gt;
gt;gt; gt;gt; (I wasn't paying good attention when I pasted the wrong version in my
gt;gt; gt;gt; post)
gt;gt; gt;gt;
gt;gt; gt;gt; ***********
gt;gt; gt;gt; Regards,
gt;gt; gt;gt; Ron
gt;gt; gt;gt;
gt;gt; gt;gt; XL2002, WinXP-Pro
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Ron Coderrequot; wrote:
gt;gt; gt;gt;
gt;gt; gt;gt;gt; First
gt;gt; gt;gt;gt; Put this formula in cell C2 and copy it down as far as you need.
gt;gt; gt;gt;gt; =IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)gt;0,quot;FOUNDquot;,quot;NOT
gt;gt; gt;gt;gt; FOUNDquot;)
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; Second
gt;gt; gt;gt;gt; Create your list of forbidden words in a blank column. For this
gt;gt; gt;gt;gt; example,
gt;gt; gt;gt;gt; I
gt;gt; gt;gt;gt; started my list in Cell E2 on Sheet2.
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; Then, Insertgt;Namegt;Create
gt;gt; gt;gt;gt; Name in Workbook: CheckList
gt;gt; gt;gt;gt; Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
gt;gt; gt;gt;gt; Click [OK]
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; That created a dynamic range name called CheckList that automatically
gt;gt; gt;gt;gt; resizes to accomodate the number of items in the list beginning in
gt;gt; gt;gt;gt; cell
gt;gt; gt;gt;gt; E2 on
gt;gt; gt;gt;gt; Sheet2.
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; Does that help?
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; ***********
gt;gt; gt;gt;gt; Regards,
gt;gt; gt;gt;gt; Ron
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; XL2002, WinXP-Pro
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; quot;guyquot; wrote:
gt;gt; gt;gt;gt;
gt;gt; gt;gt;gt; gt; cjoint.com/data/bguHFjhv8r.htm
gt;gt; gt;gt;gt; gt;
gt;gt; gt;gt;gt; gt; My problem is attached on the above link. Anyone can help? I have
gt;gt; gt;gt;gt; gt; been
gt;gt; gt;gt;gt; gt; looking for solutions for long....to make a simple search engine.
gt;gt; gt;gt;gt; gt;
gt;gt; gt;gt;gt; gt; Also, can I make Column C update automatically (show Found/NOT
gt;gt; gt;gt;gt; gt; Found)
gt;gt; gt;gt;gt; gt; when I
gt;gt; gt;gt;gt; gt; keep adding new TargetNames on Column B.
gt;gt; gt;gt;gt; gt;
gt;gt; gt;gt;gt; gt; Appreciate any help from you!!
gt;gt; gt;gt;gt; gt; THANK YOU VERY MUCH!!!
gt;gt; gt;gt;gt; gt;
gt;gt; gt;gt;gt; gt;
gt;gt; gt;gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;