Should be a easy How do I "Dan Hale" <dhale[ at ]mylinuxisp.com> 28.08.2006 17:56:40 I have a spreadsheet that I put Store names and addresses in the same cell (say E5), I need a formula so that if E5 contains "Target" or "CVS" that in cell F6 it will return a number. Should be easy can someone please show me the formula? Thanks

Re: Should be a easy How do I "Bob Phillips" <bob.NGs[ at ]somewhere.com> 28.08.2006 18:08:53 =IF(OR(ISNUMBER(FIND("Target",A1)),ISNUMBER(FIND("CVS",A1))),1,"") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dan Hale" <dhale[ at ]mylinuxisp.com> wrote in message news:uLUyptsyGHA.3440[ at ]TK2MSFTNGP06.phx.gbl... [Quoted Text] > I have a spreadsheet that I put Store names and addresses in the same cell > (say E5), I need a formula so that if E5 contains "Target" or "CVS" that in > cell F6 it will return a number. Should be easy can someone please show me > the formula? > > Thanks > >

Re: Should be a easy How do I "Dan Hale" <dhale[ at ]mylinuxisp.com> 28.08.2006 19:12:12 Thanks Now let's add something else Here is the formula real =IF(OR(ISNUMBER(FIND("WALGREENS",E5)),ISNUMBER(FIND("CVS",A1))),60,"") Now what I need to do is if the cell contains "WALGREENS" OR "CVS" return 60 like above, but if it contains "TARGET" return 120 else blank Thanks "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message news:OssOS0syGHA.2036[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > =IF(OR(ISNUMBER(FIND("Target",A1)),ISNUMBER(FIND("CVS",A1))),1,"") > > -- > HTH > > Bob Phillips > > (replace somewhere in email address with gmail if mailing direct) > > "Dan Hale" <dhale[ at ]mylinuxisp.com> wrote in message > news:uLUyptsyGHA.3440[ at ]TK2MSFTNGP06.phx.gbl... >> I have a spreadsheet that I put Store names and addresses in the same >> cell >> (say E5), I need a formula so that if E5 contains "Target" or "CVS" that > in >> cell F6 it will return a number. Should be easy can someone please show >> me >> the formula? >> >> Thanks >> >> > >

Re: Should be a easy How do I Rookie 1st class 28.08.2006 21:58:01 In F6 paste =if(E6="WALGREENS",60,if(E6="CVS",60,if(E6="TARGET",120,"")))' you can gang up to 6 if statements together. HTH Lou "Dan Hale" wrote: [Quoted Text] > Thanks > Now let's add something else > Here is the formula real > =IF(OR(ISNUMBER(FIND("WALGREENS",E5)),ISNUMBER(FIND("CVS",A1))),60,"") > Now what I need to do is if the cell contains "WALGREENS" OR "CVS" return 60 > like above, but if it contains "TARGET" return 120 else blank > > Thanks > > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message > news:OssOS0syGHA.2036[ at ]TK2MSFTNGP05.phx.gbl... > > =IF(OR(ISNUMBER(FIND("Target",A1)),ISNUMBER(FIND("CVS",A1))),1,"") > > > > -- > > HTH > > > > Bob Phillips > > > > (replace somewhere in email address with gmail if mailing direct) > > > > "Dan Hale" <dhale[ at ]mylinuxisp.com> wrote in message > > news:uLUyptsyGHA.3440[ at ]TK2MSFTNGP06.phx.gbl... > >> I have a spreadsheet that I put Store names and addresses in the same > >> cell > >> (say E5), I need a formula so that if E5 contains "Target" or "CVS" that > > in > >> cell F6 it will return a number. Should be easy can someone please show > >> me > >> the formula? > >> > >> Thanks > >> > >> > > > > > > >

Re: Should be a easy How do I Michael M 29.08.2006 00:04:01 Hi You can actually use 7 IF statements not 6.........but any more than that would suggest you use a VLOOKUP table HTH Michael M "Rookie 1st class" wrote: [Quoted Text] > In F6 paste > =if(E6="WALGREENS",60,if(E6="CVS",60,if(E6="TARGET",120,"")))' you can gang > up to 6 if statements together. > HTH Lou > > "Dan Hale" wrote: > > > Thanks > > Now let's add something else > > Here is the formula real > > =IF(OR(ISNUMBER(FIND("WALGREENS",E5)),ISNUMBER(FIND("CVS",A1))),60,"") > > Now what I need to do is if the cell contains "WALGREENS" OR "CVS" return 60 > > like above, but if it contains "TARGET" return 120 else blank > > > > Thanks > > > > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message > > news:OssOS0syGHA.2036[ at ]TK2MSFTNGP05.phx.gbl... > > > =IF(OR(ISNUMBER(FIND("Target",A1)),ISNUMBER(FIND("CVS",A1))),1,"") > > > > > > -- > > > HTH > > > > > > Bob Phillips > > > > > > (replace somewhere in email address with gmail if mailing direct) > > > > > > "Dan Hale" <dhale[ at ]mylinuxisp.com> wrote in message > > > news:uLUyptsyGHA.3440[ at ]TK2MSFTNGP06.phx.gbl... > > >> I have a spreadsheet that I put Store names and addresses in the same > > >> cell > > >> (say E5), I need a formula so that if E5 contains "Target" or "CVS" that > > > in > > >> cell F6 it will return a number. Should be easy can someone please show > > >> me > > >> the formula? > > >> > > >> Thanks > > >> > > >> > > > > > > > > > > > >

Re: Should be a easy How do I "Peo Sjoblom" <peo.sjoblom[ at ]nw^^excelsolutions.com> 29.08.2006 00:11:50 Actually you can trick Excel by dumbing down the formula =IF(E6="A",60,""&IF(E6="B",60,"")&and so on only limitation is the length of the formula, having said that I wouldn't recommend it since the formula would be very ugly -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email) "Michael M" <MichaelM[ at ]discussions.microsoft.com> wrote in message news:688C2776-2952-4B3F-BB81-B6CDCAB5F60A[ at ]microsoft.com... [Quoted Text] > Hi > You can actually use 7 IF statements not 6.........but any more than that > would suggest you use a VLOOKUP table > > HTH > Michael M > > "Rookie 1st class" wrote: > >> In F6 paste >> =if(E6="WALGREENS",60,if(E6="CVS",60,if(E6="TARGET",120,"")))' you can >> gang >> up to 6 if statements together. >> HTH Lou >> >> "Dan Hale" wrote: >> >> > Thanks >> > Now let's add something else >> > Here is the formula real >> > =IF(OR(ISNUMBER(FIND("WALGREENS",E5)),ISNUMBER(FIND("CVS",A1))),60,"") >> > Now what I need to do is if the cell contains "WALGREENS" OR "CVS" >> > return 60 >> > like above, but if it contains "TARGET" return 120 else blank >> > >> > Thanks >> > >> > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message >> > news:OssOS0syGHA.2036[ at ]TK2MSFTNGP05.phx.gbl... >> > > =IF(OR(ISNUMBER(FIND("Target",A1)),ISNUMBER(FIND("CVS",A1))),1,"") >> > > >> > > -- >> > > HTH >> > > >> > > Bob Phillips >> > > >> > > (replace somewhere in email address with gmail if mailing direct) >> > > >> > > "Dan Hale" <dhale[ at ]mylinuxisp.com> wrote in message >> > > news:uLUyptsyGHA.3440[ at ]TK2MSFTNGP06.phx.gbl... >> > >> I have a spreadsheet that I put Store names and addresses in the >> > >> same >> > >> cell >> > >> (say E5), I need a formula so that if E5 contains "Target" or "CVS" >> > >> that >> > > in >> > >> cell F6 it will return a number. Should be easy can someone please >> > >> show >> > >> me >> > >> the formula? >> > >> >> > >> Thanks >> > >> >> > >> >> > > >> > > >> > >> > >> >

Re: Should be a easy How do I "Dan Hale" <dhale[ at ]mylinuxisp.com> 29.08.2006 02:26:25 Ok that's great to know, but the problem is that "WALGREENS" or "CVS" or "Target" are not the only text in the cell so I need a formula that would work like yours but checks if the cell contains those words? "Rookie 1st class" <Rookie1stClass[ at ]SpamThis> wrote in message news:7350B008-C6F9-4950-A33A-99A31A84127E[ at ]microsoft.com... [Quoted Text] > In F6 paste > =if(E6="WALGREENS",60,if(E6="CVS",60,if(E6="TARGET",120,"")))' you can > gang > up to 6 if statements together. > HTH Lou > > "Dan Hale" wrote: > >> Thanks >> Now let's add something else >> Here is the formula real >> =IF(OR(ISNUMBER(FIND("WALGREENS",E5)),ISNUMBER(FIND("CVS",A1))),60,"") >> Now what I need to do is if the cell contains "WALGREENS" OR "CVS" return >> 60 >> like above, but if it contains "TARGET" return 120 else blank >> >> Thanks >> >> "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message >> news:OssOS0syGHA.2036[ at ]TK2MSFTNGP05.phx.gbl... >> > =IF(OR(ISNUMBER(FIND("Target",A1)),ISNUMBER(FIND("CVS",A1))),1,"") >> > >> > -- >> > HTH >> > >> > Bob Phillips >> > >> > (replace somewhere in email address with gmail if mailing direct) >> > >> > "Dan Hale" <dhale[ at ]mylinuxisp.com> wrote in message >> > news:uLUyptsyGHA.3440[ at ]TK2MSFTNGP06.phx.gbl... >> >> I have a spreadsheet that I put Store names and addresses in the same >> >> cell >> >> (say E5), I need a formula so that if E5 contains "Target" or "CVS" >> >> that >> > in >> >> cell F6 it will return a number. Should be easy can someone please >> >> show >> >> me >> >> the formula? >> >> >> >> Thanks >> >> >> >> >> > >> > >> >> >>

Re: Should be a easy How do I Michael M 29.08.2006 02:38:01 I still think you need a VLOOKUP table Go to somewhere where you have some space, say G1. In column G list all the stores In column H list their values. Go back to where you want the formula and use: =VLOOKUP(E6,G1:H100,2, FALSE) HTH Michael M "Dan Hale" wrote: [Quoted Text] > Ok that's great to know, but the problem is that "WALGREENS" or "CVS" or > "Target" are not the only text in the cell so I need a formula that would > work like yours but checks if the cell contains those words? > > > "Rookie 1st class" <Rookie1stClass[ at ]SpamThis> wrote in message > news:7350B008-C6F9-4950-A33A-99A31A84127E[ at ]microsoft.com... > > In F6 paste > > =if(E6="WALGREENS",60,if(E6="CVS",60,if(E6="TARGET",120,"")))' you can > > gang > > up to 6 if statements together. > > HTH Lou > > > > "Dan Hale" wrote: > > > >> Thanks > >> Now let's add something else > >> Here is the formula real > >> =IF(OR(ISNUMBER(FIND("WALGREENS",E5)),ISNUMBER(FIND("CVS",A1))),60,"") > >> Now what I need to do is if the cell contains "WALGREENS" OR "CVS" return > >> 60 > >> like above, but if it contains "TARGET" return 120 else blank > >> > >> Thanks > >> > >> "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message > >> news:OssOS0syGHA.2036[ at ]TK2MSFTNGP05.phx.gbl... > >> > =IF(OR(ISNUMBER(FIND("Target",A1)),ISNUMBER(FIND("CVS",A1))),1,"") > >> > > >> > -- > >> > HTH > >> > > >> > Bob Phillips > >> > > >> > (replace somewhere in email address with gmail if mailing direct) > >> > > >> > "Dan Hale" <dhale[ at ]mylinuxisp.com> wrote in message > >> > news:uLUyptsyGHA.3440[ at ]TK2MSFTNGP06.phx.gbl... > >> >> I have a spreadsheet that I put Store names and addresses in the same > >> >> cell > >> >> (say E5), I need a formula so that if E5 contains "Target" or "CVS" > >> >> that > >> > in > >> >> cell F6 it will return a number. Should be easy can someone please > >> >> show > >> >> me > >> >> the formula? > >> >> > >> >> Thanks > >> >> > >> >> > >> > > >> > > >> > >> > >> > > >

Re: Should be a easy How do I "Bob Phillips" <bob.NGs[ at ]somewhere.com> 29.08.2006 07:49:19 Well actually you can have 8, 7 nested. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Michael M" <MichaelM[ at ]discussions.microsoft.com> wrote in message news:688C2776-2952-4B3F-BB81-B6CDCAB5F60A[ at ]microsoft.com... [Quoted Text] > Hi > You can actually use 7 IF statements not 6.........but any more than that > would suggest you use a VLOOKUP table > > HTH > Michael M > > "Rookie 1st class" wrote: > > > In F6 paste > > =if(E6="WALGREENS",60,if(E6="CVS",60,if(E6="TARGET",120,"")))' you can gang > > up to 6 if statements together. > > HTH Lou > > > > "Dan Hale" wrote: > > > > > Thanks > > > Now let's add something else > > > Here is the formula real > > > =IF(OR(ISNUMBER(FIND("WALGREENS",E5)),ISNUMBER(FIND("CVS",A1))),60,"") > > > Now what I need to do is if the cell contains "WALGREENS" OR "CVS" return 60 > > > like above, but if it contains "TARGET" return 120 else blank > > > > > > Thanks > > > > > > "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message > > > news:OssOS0syGHA.2036[ at ]TK2MSFTNGP05.phx.gbl... > > > > =IF(OR(ISNUMBER(FIND("Target",A1)),ISNUMBER(FIND("CVS",A1))),1,"") > > > > > > > > -- > > > > HTH > > > > > > > > Bob Phillips > > > > > > > > (replace somewhere in email address with gmail if mailing direct) > > > > > > > > "Dan Hale" <dhale[ at ]mylinuxisp.com> wrote in message > > > > news:uLUyptsyGHA.3440[ at ]TK2MSFTNGP06.phx.gbl... > > > >> I have a spreadsheet that I put Store names and addresses in the same > > > >> cell > > > >> (say E5), I need a formula so that if E5 contains "Target" or "CVS" that > > > > in > > > >> cell F6 it will return a number. Should be easy can someone please show > > > >> me > > > >> the formula? > > > >> > > > >> Thanks > > > >> > > > >> > > > > > > > > > > > > > > > > >

Re: Should be a easy How do I "Bob Phillips" <bob.NGs[ at ]somewhere.com> 29.08.2006 07:53:36 =IF(OR(ISNUMBER(FIND("WALGREENS",E5)),ISNUMBER(FIND("CVS",E5))),60, IF(ISNUMBER(FIND("TARGET",E5)),120,"")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dan Hale" <dhale[ at ]mylinuxisp.com> wrote in message news:OJ1GiKxyGHA.2636[ at ]TK2MSFTNGP06.phx.gbl... [Quoted Text] > Ok that's great to know, but the problem is that "WALGREENS" or "CVS" or > "Target" are not the only text in the cell so I need a formula that would > work like yours but checks if the cell contains those words? > > > "Rookie 1st class" <Rookie1stClass[ at ]SpamThis> wrote in message > news:7350B008-C6F9-4950-A33A-99A31A84127E[ at ]microsoft.com... > > In F6 paste > > =if(E6="WALGREENS",60,if(E6="CVS",60,if(E6="TARGET",120,"")))' you can > > gang > > up to 6 if statements together. > > HTH Lou > > > > "Dan Hale" wrote: > > > >> Thanks > >> Now let's add something else > >> Here is the formula real > >> =IF(OR(ISNUMBER(FIND("WALGREENS",E5)),ISNUMBER(FIND("CVS",A1))),60,"") > >> Now what I need to do is if the cell contains "WALGREENS" OR "CVS" return > >> 60 > >> like above, but if it contains "TARGET" return 120 else blank > >> > >> Thanks > >> > >> "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message > >> news:OssOS0syGHA.2036[ at ]TK2MSFTNGP05.phx.gbl... > >> > =IF(OR(ISNUMBER(FIND("Target",A1)),ISNUMBER(FIND("CVS",A1))),1,"") > >> > > >> > -- > >> > HTH > >> > > >> > Bob Phillips > >> > > >> > (replace somewhere in email address with gmail if mailing direct) > >> > > >> > "Dan Hale" <dhale[ at ]mylinuxisp.com> wrote in message > >> > news:uLUyptsyGHA.3440[ at ]TK2MSFTNGP06.phx.gbl... > >> >> I have a spreadsheet that I put Store names and addresses in the same > >> >> cell > >> >> (say E5), I need a formula so that if E5 contains "Target" or "CVS" > >> >> that > >> > in > >> >> cell F6 it will return a number. Should be easy can someone please > >> >> show > >> >> me > >> >> the formula? > >> >> > >> >> Thanks > >> >> > >> >> > >> > > >> > > >> > >> > >> > >