sheet reference

[Bob]

Hello Group -

I have a small data base and I'm tweaking it a little. I
have my search criteria and the records all on the same
sheet but I want to put the search formulas on a new and
separate sheet. I'm having trouble with the sheet
reference in the formula:

=IF(B1="","",SUMPRODUCT(--(ISNUMBER(SEARCH
(" "&B1&" "," "&B11:B2297&" ")))))

I need to change the search range to sheet name dbase:

dbase!" "&B11:B2297&" " isn't working.

I know it's because of the leading space " ".

I've tried all kinds of combinations of quotes and & but
nothing works.

Any ideas?

Thanks
Bob P.

 

[Vasant Nanavati]

Perhaps (untested):

=IF(B1="","",SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" ",dbase!B11:B2297)))))

 

[Bob]

Thanks Vasant, but if I don't include the leading and
trailing spaces, the formula fails.

All this formula does is count the number of matches. The
leading and trailing spaces ensure exact matches and is
100% reliable.

Any other thoughts? I've even tried using Indirect without
success.

Bob P.

-----Original Message-----
Perhaps (untested):

=IF(B1="","",SUMPRODUCT(--(ISNUMBER(SEARCH

(" "&B1&" ",dbase!B11:B2297)))))

 

[Vasant Nanavati]

Bob, try:

=IF(B1="","",SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" ","
"&INDIRECT("dbase!B11:B25")&" ")))))

 

[Soo Cheon Jheong]

Bob,

Try the following formula:

=IF(B1="","",COUNTIF(B11:B2297,"* "&B1&" *"))


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[Bob]

That did the trick. Thank you!

Bob P.

-----Original Message-----
Bob, try:

=IF(B1="","",SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" ","
"&INDIRECT("dbase!B11:B25")&" ")))))

--

Vasant






.

 

[Bob]

Soo, thanks for the suggestion but unfortunately, that
formula fails.

Believe me when I say that I've tried any number of
combinations of Countif's with no success. The formula
that I'm using now is the *only* one that works 100% of
the time!

Bob P.

 

[Frank Kabel]

Hi
the following should work also:

=IF(B1="","",SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" "," "&
dbase!B11:B25 &" ")))))

 

[Bob]

Thanks Frank. Yes, that also works! I wonder why I didn't
try that? Maybe it was too obvious?

Bob P.