| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF FORMULA
I need a formula for the following:
If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
=IF(B19="STG","IMC COE",IF(B19="SWG","SWG IMC",IF(B19="S&D","S&D
IMC",IF(B19="IGF","IGF IMC",IF(B19="GTS","GTS IMC",IF(B19="GBS","GBS IMC",IF(B19="Alliances","Alliances IMC" ))))))) Here B19=D6 and type the above in D7. However we have a limitation of using only 7 ifs. "Karen Smith" wrote: I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
Hi Karen,
You could write a really complex IF statement (Excel limits you to 7 nested layers), OR, (and this is the way I would do it) you could create a table with all possible values listed and then use a VLOOKUP formula to pull in what you're looking for. Start by creating your table. It can be on the same sheet or in a separate sheet. Let's assume you will put it on a separate sheet. I will assume Sheet2. Your table should look something like this: Column A Column B STG IMC COE SWG SWG IMC S&D S&D IMC IGF IGF IMC GTS GTS IMC GBS GBS IMC Alliances Alliances IMC Then back on your other sheet, enter the following formula in cell D7: =VLOOKUP(D6,Sheet2!A:B,2,FALSE) If you put your table on something other than Sheet2, then just make sure your change the formula to match. "Karen Smith" wrote: I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
The best approach would be to list the possibilities for D6 (STG, SWG, etc)
down one column somewhere and the required results for D7 down the next column. (For example, this could be in X101:Y107, or anywhere you choose, even on another sheet.) Then in D7 you would use the formula: =VLOOKUP(D6,X101:Y107,2,FALSE) This way, it's easy to check and modify/extend. (A multiple IF statement would be limited to 7 nestings, and difficult to read.) "Karen Smith" wrote in message ... I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
Try:
=IF(D6="STG","IMC COE",IF(OR(D6="SWG",D6="S&D",D6="IGF",D6="GTS",D6= "GBS",D6="Alliance"),D6&" IMC","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Karen Smith" wrote in message ... I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
Look in the help index for CHOOSE or LOOKUP
-- Don Guillett Microsoft MVP Excel SalesAid Software "Karen Smith" wrote in message ... I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
If you don't wish to create a datalist, here's one way:
=LOOKUP(D6,{"STG","SWG","S&D","IGF","GTS","GBS","A lliances";"IMC COE","SWG IMC","S&D IMC","IGF IMC","GTS IMC","GBS IMC","Alliances IMC"}) Entered in D7. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Karen Smith" wrote in message ... I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
Nice !
And that's not even considering I forgot to sort my list.<bg -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Don Guillett" wrote in message ... Or =IF(D6="stg","IMC COE",D6&" IMC") -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Look in the help index for CHOOSE or LOOKUP -- Don Guillett Microsoft MVP Excel SalesAid Software "Karen Smith" wrote in message ... I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
Please note that the values in LOOKUP() much be placed in ascending order for
it to work as expected. "RagDyeR" wrote: If you don't wish to create a datalist, here's one way: =LOOKUP(D6,{"STG","SWG","S&D","IGF","GTS","GBS","A lliances";"IMC COE","SWG IMC","S&D IMC","IGF IMC","GTS IMC","GBS IMC","Alliances IMC"}) Entered in D7. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Karen Smith" wrote in message ... I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
I know.<bg
Didn't you see the mention of my "faux pas" in my post to Don? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ed Cones" wrote in message ... Please note that the values in LOOKUP() much be placed in ascending order for it to work as expected. "RagDyeR" wrote: If you don't wish to create a datalist, here's one way: =LOOKUP(D6,{"STG","SWG","S&D","IGF","GTS","GBS","A lliances";"IMC COE","SWG IMC","S&D IMC","IGF IMC","GTS IMC","GBS IMC","Alliances IMC"}) Entered in D7. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Karen Smith" wrote in message ... I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
No. Probably because I was still trying to figure out why the heck it
wouldn't work when you posted it. ;o) "RagDyer" wrote: I know.<bg Didn't you see the mention of my "faux pas" in my post to Don? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ed Cones" wrote in message ... Please note that the values in LOOKUP() much be placed in ascending order for it to work as expected. "RagDyeR" wrote: If you don't wish to create a datalist, here's one way: =LOOKUP(D6,{"STG","SWG","S&D","IGF","GTS","GBS","A lliances";"IMC COE","SWG IMC","S&D IMC","IGF IMC","GTS IMC","GBS IMC","Alliances IMC"}) Entered in D7. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Karen Smith" wrote in message ... I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
You're right ... I should have corrected it as soon as I realized my
mistake. However, Don's solution was so vividly concise, and posted above mine, I figured anyone viewing this thread would choose to test and/or use his right off. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Ed Cones" wrote in message ... No. Probably because I was still trying to figure out why the heck it wouldn't work when you posted it. ;o) "RagDyer" wrote: I know.<bg Didn't you see the mention of my "faux pas" in my post to Don? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ed Cones" wrote in message ... Please note that the values in LOOKUP() much be placed in ascending order for it to work as expected. "RagDyeR" wrote: If you don't wish to create a datalist, here's one way: =LOOKUP(D6,{"STG","SWG","S&D","IGF","GTS","GBS","A lliances";"IMC COE","SWG IMC","S&D IMC","IGF IMC","GTS IMC","GBS IMC","Alliances IMC"}) Entered in D7. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Karen Smith" wrote in message ... I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
My post was not intended to chastise. I was laughing at myself, which is
very easy to do. You have a terrific Christmas. "RagDyeR" wrote: You're right ... I should have corrected it as soon as I realized my mistake. However, Don's solution was so vividly concise, and posted above mine, I figured anyone viewing this thread would choose to test and/or use his right off. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Ed Cones" wrote in message ... No. Probably because I was still trying to figure out why the heck it wouldn't work when you posted it. ;o) "RagDyer" wrote: I know.<bg Didn't you see the mention of my "faux pas" in my post to Don? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ed Cones" wrote in message ... Please note that the values in LOOKUP() much be placed in ascending order for it to work as expected. "RagDyeR" wrote: If you don't wish to create a datalist, here's one way: =LOOKUP(D6,{"STG","SWG","S&D","IGF","GTS","GBS","A lliances";"IMC COE","SWG IMC","S&D IMC","IGF IMC","GTS IMC","GBS IMC","Alliances IMC"}) Entered in D7. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Karen Smith" wrote in message ... I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMULA
And a Very Merry Christmas to you too!
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ed Cones" wrote in message ... My post was not intended to chastise. I was laughing at myself, which is very easy to do. You have a terrific Christmas. "RagDyeR" wrote: You're right ... I should have corrected it as soon as I realized my mistake. However, Don's solution was so vividly concise, and posted above mine, I figured anyone viewing this thread would choose to test and/or use his right off. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Ed Cones" wrote in message ... No. Probably because I was still trying to figure out why the heck it wouldn't work when you posted it. ;o) "RagDyer" wrote: I know.<bg Didn't you see the mention of my "faux pas" in my post to Don? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ed Cones" wrote in message ... Please note that the values in LOOKUP() much be placed in ascending order for it to work as expected. "RagDyeR" wrote: If you don't wish to create a datalist, here's one way: =LOOKUP(D6,{"STG","SWG","S&D","IGF","GTS","GBS","A lliances";"IMC COE","SWG IMC","S&D IMC","IGF IMC","GTS IMC","GBS IMC","Alliances IMC"}) Entered in D7. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Karen Smith" wrote in message ... I need a formula for the following: If D6=STG, then place "IMC COE" in cell D7 If D6=SWG, then place "SWG IMC" in cell D7 If D6=S&D, then place "S&D IMC" in cell D7 If D6=IGF, then place "IGF IMC" in cell D7 If D6=GTS, then place "GTS IMC" in cell D7 If D6=GBS, then place "GBS IMC" in cell D7 If D6=Alliances, then place "Alliances IMC" in cell D7 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
