OPPOSITE FORMULA

I was provided this formula to not look for the word Repair Clin:

=IF(O31="","",IF(AND(TRIM(B31)<>"Closed",NOT(ISNUMBER(SEARCH("Repair
Clin",S31)))), O31+ROW()/10^10,""))

Now, I need the opposite. I want it to look for the word Warranty item.

Also, can anyone recommend a good Excel book.

Thanks

> ... need the opposite. want to look for the word Warranty item.

Try:

=IF(O31="","",IF(AND(TRIM(B31)<>"Closed",ISNUMBER(SEARCH("Warranty
item",S31))),O31+ROW()/10^10,""))

> Also, can anyone recommend a good Excel book.

Try browsing the comprehensive list in Debra's page at:
http://www.contextures.com/xlbooks.html

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"roy.okinawa" <royokinawa@discussions.microsoft.com> wrote in message
news:DC050660-59FC-4B72-B0E8-567C53E735DA@microsoft.com...
> I was provided this formula to not look for the word Repair Clin:
>
> =IF(O31="","",IF(AND(TRIM(B31)<>"Closed",NOT(ISNUMBER(SEARCH("Repair
> Clin",S31)))), O31+ROW()/10^10,""))
>
> Now, I need the opposite. I want it to look for the word Warranty item.
>
> Also, can anyone recommend a good Excel book.
>
> Thanks

Hi!

Check out this book list:

http://contextures.com/xlbooks.html

As for your "opposite" formula:

That looks like one of Max's formulas?

Maybe this:

=IF(O31="","",IF(AND(TRIM(B31)<>"Closed",ISNUMBER(SEARCH("Warranty
item",S31))), O31+ROW()/10^10,""))

Biff

"roy.okinawa" <royokinawa@discussions.microsoft.com> wrote in message
news:DC050660-59FC-4B72-B0E8-567C53E735DA@microsoft.com...
>I was provided this formula to not look for the word Repair Clin:
>
> =IF(O31="","",IF(AND(TRIM(B31)<>"Closed",NOT(ISNUMBER(SEARCH("Repair
> Clin",S31)))), O31+ROW()/10^10,""))
>
> Now, I need the opposite. I want it to look for the word Warranty item.
>
> Also, can anyone recommend a good Excel book.
>
> Thanks

"Biff" wrote:
> That looks like one of Max's formulas?

It is <g>. Had quite a long discussion
with the OP, Roy, over the past couple of days.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Max,

I thank you again for the formula and the website. One last one and I think
the worksheet/book will be complete:

Using the same criteria, delete column B from the equation.


"Max" wrote:

> > ... need the opposite. want to look for the word Warranty item.
>
> Try:
>
> =IF(O31="","",IF(AND(TRIM(B31)<>"Closed",ISNUMBER(SEARCH("Warranty
> item",S31))),O31+ROW()/10^10,""))
>
> > Also, can anyone recommend a good Excel book.
>
> Try browsing the comprehensive list in Debra's page at:
> http://www.contextures.com/xlbooks.html
>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "roy.okinawa" <royokinawa@discussions.microsoft.com> wrote in message
> news:DC050660-59FC-4B72-B0E8-567C53E735DA@microsoft.com...
> > I was provided this formula to not look for the word Repair Clin:
> >
> > =IF(O31="","",IF(AND(TRIM(B31)<>"Closed",NOT(ISNUMBER(SEARCH("Repair
> > Clin",S31)))), O31+ROW()/10^10,""))
> >
> > Now, I need the opposite. I want it to look for the word Warranty item.
> >
> > Also, can anyone recommend a good Excel book.
> >
> > Thanks
>
>
>

> Using the same criteria, delete column B from the equation.

Try:

=IF(O31="","",IF(ISNUMBER(SEARCH("Warranty item",S31)),O31+ROW()/10^10,""))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"roy.okinawa" <royokinawa@discussions.microsoft.com> wrote in message
news:60BFE369-0C5C-4CCA-BCBF-F416B42AD6AC@microsoft.com...
> Max,
>
> I thank you again for the formula and the website. One last one and I
think
> the worksheet/book will be complete:
>
> Using the same criteria, delete column B from the equation.

That did it. Again thank you for the knowledge. With the web site I hope to
become more proficient.



"Max" wrote:

> "Biff" wrote:
> > That looks like one of Max's formulas?
>
> It is <g>. Had quite a long discussion
> with the OP, Roy, over the past couple of days.
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
>