software

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns quot;ADKquot; if the ref cell contains Dean, dean, Dean's, dean's or returns
quot;DJMquot; if the same ref cell contains Dave, dave, Dave's, dave's, and returns
quot;MWPquot; if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return quot;ADKquot; or if it finds an a in the 2nd position then return quot;DJMquot;. Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

=IF(LOWER(LEFT(A1,4))=quot;deanquot;,quot;ADKquot;,IF(LOWER(LEFT(A 1,4))=quot;davequot;,quot;DJMquot;,quot;MWPquot;))

--
Kind regards,

Niek Otten

quot;CMIConniequot; gt; wrote in message
...
gt;I think I am making this more difficult than it is, but I can't sort it out
gt; in my mind. I need a formula that does the following:
gt; Returns quot;ADKquot; if the ref cell contains Dean, dean, Dean's, dean's or
gt; returns
gt; quot;DJMquot; if the same ref cell contains Dave, dave, Dave's, dave's, and
gt; returns
gt; quot;MWPquot; if there is anything else but Dean or Dave or David in the cell.
gt; I'm
gt; thinking that I can use the logic if it finds an e in the 2nd position,
gt; return quot;ADKquot; or if it finds an a in the 2nd position then return quot;DJMquot;.
gt; Am I
gt; on the right track with this? It seems like there are too many logigals
gt; for
gt; an if/then formula?
gt; Any help would be GREATLY appreciated! (I am using Excel 2000)
On Thu, 2 Mar 2006 08:03:27 -0800, CMIConnie wrote:

gt; I think I am making this more difficult than it is, but I can't sort it out
gt; in my mind. I need a formula that does the following:
gt; Returns quot;ADKquot; if the ref cell contains Dean, dean, Dean's, dean's or returns
gt; quot;DJMquot; if the same ref cell contains Dave, dave, Dave's, dave's, and returns
gt; quot;MWPquot; if there is anything else but Dean or Dave or David in the cell. I'm
gt; thinking that I can use the logic if it finds an e in the 2nd position,
gt; return quot;ADKquot; or if it finds an a in the 2nd position then return quot;DJMquot;. Am I
gt; on the right track with this? It seems like there are too many logigals for
gt; an if/then formula?
gt; Any help would be GREATLY appreciated! (I am using Excel 2000)

-------------------------

How about:

=if(ucase(left(A1,4))=quot;DEANquot;,quot;ADKquot;,if(ucase(left(A 1,4))=quot;DAVEquot;,quot;DJMquot;,quot;MWPquot;))

Bill

Hi Bill,

You can see I'm lazier than you a I tried to avoid holding the SHIFT key
:-)

--
Kind regards,

Niek Otten

quot;Bill Martinquot; gt; wrote in message
...
gt; On Thu, 2 Mar 2006 08:03:27 -0800, CMIConnie wrote:
gt;
gt;gt; I think I am making this more difficult than it is, but I can't sort it
gt;gt; out
gt;gt; in my mind. I need a formula that does the following:
gt;gt; Returns quot;ADKquot; if the ref cell contains Dean, dean, Dean's, dean's or
gt;gt; returns
gt;gt; quot;DJMquot; if the same ref cell contains Dave, dave, Dave's, dave's, and
gt;gt; returns
gt;gt; quot;MWPquot; if there is anything else but Dean or Dave or David in the cell.
gt;gt; I'm
gt;gt; thinking that I can use the logic if it finds an e in the 2nd position,
gt;gt; return quot;ADKquot; or if it finds an a in the 2nd position then return quot;DJMquot;.
gt;gt; Am I
gt;gt; on the right track with this? It seems like there are too many logigals
gt;gt; for
gt;gt; an if/then formula?
gt;gt; Any help would be GREATLY appreciated! (I am using Excel 2000)
gt;
gt; -------------------------
gt;
gt; How about:
gt;
gt; =if(ucase(left(A1,4))=quot;DEANquot;,quot;ADKquot;,if(ucase(left(A 1,4))=quot;DAVEquot;,quot;DJMquot;,quot;MWPquot;))
gt;
gt; Bill
On Thu, 2 Mar 2006 11:14:47 -0500, Bill Martin wrote:

gt; On Thu, 2 Mar 2006 08:03:27 -0800, CMIConnie wrote:
gt;
gt;gt; I think I am making this more difficult than it is, but I can't sort it out
gt;gt; in my mind. I need a formula that does the following:
gt;gt; Returns quot;ADKquot; if the ref cell contains Dean, dean, Dean's, dean's or returns
gt;gt; quot;DJMquot; if the same ref cell contains Dave, dave, Dave's, dave's, and returns
gt;gt; quot;MWPquot; if there is anything else but Dean or Dave or David in the cell. I'm
gt;gt; thinking that I can use the logic if it finds an e in the 2nd position,
gt;gt; return quot;ADKquot; or if it finds an a in the 2nd position then return quot;DJMquot;. Am I
gt;gt; on the right track with this? It seems like there are too many logigals for
gt;gt; an if/then formula?
gt;gt; Any help would be GREATLY appreciated! (I am using Excel 2000)
gt;
gt; -------------------------
gt;
gt; How about:
gt;
gt; =if(ucase(left(A1,4))=quot;DEANquot;,quot;ADKquot;,if(ucase(left(A 1,4))=quot;DAVEquot;,quot;DJMquot;,quot;MWPquot;))
gt;
gt; Bill

-------------------

Oops -- for a formula, replace the quot;ucasequot; with quot;upperquot;. I'm confusing
formula words with VBA words. Sorry... (and why are they different
anyhow?)

Bill

If the cell can contain the specified text anywhere in the cell, like in the
following examples:
ADean
Ben dean bean
The Dean's list
hi Dave
to dave and ben
is this David?

Then, try this:
For text in A1
B1:
=CHOOSE(SUMPRODUCT(COUNTIF(A1,{quot;*dean*quot;,quot;*dav*quot;})* {1,2}) 1,quot;MWPquot;,quot;ADKquot;,quot;DJMquot;,quot;Has both!quot;)

Does that help?

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

XL2002, WinXP-Proquot;CMIConniequot; wrote:

gt; I think I am making this more difficult than it is, but I can't sort it out
gt; in my mind. I need a formula that does the following:
gt; Returns quot;ADKquot; if the ref cell contains Dean, dean, Dean's, dean's or returns
gt; quot;DJMquot; if the same ref cell contains Dave, dave, Dave's, dave's, and returns
gt; quot;MWPquot; if there is anything else but Dean or Dave or David in the cell. I'm
gt; thinking that I can use the logic if it finds an e in the 2nd position,
gt; return quot;ADKquot; or if it finds an a in the 2nd position then return quot;DJMquot;. Am I
gt; on the right track with this? It seems like there are too many logigals for
gt; an if/then formula?
gt; Any help would be GREATLY appreciated! (I am using Excel 2000)

So should quot;Davidquot; return DJM, too (based on quot;anything else but Dean or
Dave or Davidquot;)?

Should quot;Deannaquot; return MWP?

If so, this should work for you:

=IF(LEFT(SUBSTITUTE(A1amp;quot; quot;,quot;'squot;,quot;quot;),5)=quot;dean quot;,quot;ADKquot;,
IF(OR(LEFT(SUBSTITUTE(A1amp;quot; quot;,quot;'squot;,quot;quot;),5)=quot;dave quot;,A1=quot;Davidquot;),
quot;DJMquot;,quot;JWMquot;))In article gt;,
CMIConnie gt; wrote:

gt; I think I am making this more difficult than it is, but I can't sort it out
gt; in my mind. I need a formula that does the following:
gt; Returns quot;ADKquot; if the ref cell contains Dean, dean, Dean's, dean's or returns
gt; quot;DJMquot; if the same ref cell contains Dave, dave, Dave's, dave's, and returns
gt; quot;MWPquot; if there is anything else but Dean or Dave or David in the cell. I'm
gt; thinking that I can use the logic if it finds an e in the 2nd position,
gt; return quot;ADKquot; or if it finds an a in the 2nd position then return quot;DJMquot;. Am I
gt; on the right track with this? It seems like there are too many logigals for
gt; an if/then formula?
gt; Any help would be GREATLY appreciated! (I am using Excel 2000)

Just a note - the UCASE()'s are unnecessary in this formula since XL's
comparisons are case insensitive.

See my other post for a different answer - the OP's problem statement
seemed rather ambiguous.

In article gt;,
Bill Martin gt; wrote:

gt; How about:
gt;
gt; =if(ucase(left(A1,4))=quot;DEANquot;,quot;ADKquot;,if(ucase(left(A 1,4))=quot;DAVEquot;,quot;DJMquot;,quot;MWPquot;))

Thank you for your reply! It steered me in the right direction and saved me
oodles of time!

quot;Niek Ottenquot; wrote:

gt; =IF(LOWER(LEFT(A1,4))=quot;deanquot;,quot;ADKquot;,IF(LOWER(LEFT(A 1,4))=quot;davequot;,quot;DJMquot;,quot;MWPquot;))
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;CMIConniequot; gt; wrote in message
gt; ...
gt; gt;I think I am making this more difficult than it is, but I can't sort it out
gt; gt; in my mind. I need a formula that does the following:
gt; gt; Returns quot;ADKquot; if the ref cell contains Dean, dean, Dean's, dean's or
gt; gt; returns
gt; gt; quot;DJMquot; if the same ref cell contains Dave, dave, Dave's, dave's, and
gt; gt; returns
gt; gt; quot;MWPquot; if there is anything else but Dean or Dave or David in the cell.
gt; gt; I'm
gt; gt; thinking that I can use the logic if it finds an e in the 2nd position,
gt; gt; return quot;ADKquot; or if it finds an a in the 2nd position then return quot;DJMquot;.
gt; gt; Am I
gt; gt; on the right track with this? It seems like there are too many logigals
gt; gt; for
gt; gt; an if/then formula?
gt; gt; Any help would be GREATLY appreciated! (I am using Excel 2000)
gt;
gt;
gt;

Thank you for your reply! It steered me in the right direction and saved me
oodles of time!

quot;Bill Martinquot; wrote:

gt; On Thu, 2 Mar 2006 08:03:27 -0800, CMIConnie wrote:
gt;
gt; gt; I think I am making this more difficult than it is, but I can't sort it out
gt; gt; in my mind. I need a formula that does the following:
gt; gt; Returns quot;ADKquot; if the ref cell contains Dean, dean, Dean's, dean's or returns
gt; gt; quot;DJMquot; if the same ref cell contains Dave, dave, Dave's, dave's, and returns
gt; gt; quot;MWPquot; if there is anything else but Dean or Dave or David in the cell. I'm
gt; gt; thinking that I can use the logic if it finds an e in the 2nd position,
gt; gt; return quot;ADKquot; or if it finds an a in the 2nd position then return quot;DJMquot;. Am I
gt; gt; on the right track with this? It seems like there are too many logigals for
gt; gt; an if/then formula?
gt; gt; Any help would be GREATLY appreciated! (I am using Excel 2000)
gt;
gt; -------------------------
gt;
gt; How about:
gt;
gt; =if(ucase(left(A1,4))=quot;DEANquot;,quot;ADKquot;,if(ucase(left(A 1,4))=quot;DAVEquot;,quot;DJMquot;,quot;MWPquot;))
gt;
gt; Bill
gt;