Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Formula When Contains Partial Word
Hi everyone,
I'm trying to link these two sheets together as far as making them easier to integrate. I have this main sheet thats full of every group that will be coming to our establishment in the next 20 years. These groups are named for example "Microsoft Oct 09", "Microsoft Dec 11", etc - From a VLookup it pulls if they have a deposit or not from a hidden area - From an IF formula, it pulls if it is a certain sales managers initials and returns "SOCIAL RMS". I want to integrate a third formula to do the following: I have a small list (about 10 or so) of company names only - So it says "Microsoft". These are deposits that stay on file in a separate area of our accounting team. I want add a column with the formula that says "If the group name contains the word Microsoft, then say rolling deposit". What I'm having difficulty with is getting it to pull it if it CONTAINS the word. Please let me know if you have any suggestions for enhancing my sheet. BACKGROUND ON SHEET: COLUMNS: Group code, Group name, Sales person, (1) VLOOKUP to see if deposit on file in alternate hidden area, (2) IF ERROR in prev column, (3) IF prev col = true return 0 if false return deposit amount, and currently my last column (the only one unhidden since the sales person) is an (4) if the salesperson is MVW return value of social rms- if not return deposit amount from col 3 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Formula When Contains Partial Word
=if(A4-"*Microsoft*","Rolling deposit","")
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "zeetoe04" wrote in message ... Hi everyone, I'm trying to link these two sheets together as far as making them easier to integrate. I have this main sheet thats full of every group that will be coming to our establishment in the next 20 years. These groups are named for example "Microsoft Oct 09", "Microsoft Dec 11", etc - From a VLookup it pulls if they have a deposit or not from a hidden area - From an IF formula, it pulls if it is a certain sales managers initials and returns "SOCIAL RMS". I want to integrate a third formula to do the following: I have a small list (about 10 or so) of company names only - So it says "Microsoft". These are deposits that stay on file in a separate area of our accounting team. I want add a column with the formula that says "If the group name contains the word Microsoft, then say rolling deposit". What I'm having difficulty with is getting it to pull it if it CONTAINS the word. Please let me know if you have any suggestions for enhancing my sheet. BACKGROUND ON SHEET: COLUMNS: Group code, Group name, Sales person, (1) VLOOKUP to see if deposit on file in alternate hidden area, (2) IF ERROR in prev column, (3) IF prev col = true return 0 if false return deposit amount, and currently my last column (the only one unhidden since the sales person) is an (4) if the salesperson is MVW return value of social rms- if not return deposit amount from col 3 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Formula When Contains Partial Word
It still comes up as " " as opposed to "Rolling Deposit" despite the
asterisks. I'm assuming you meant (A4="*, not the negative sign. The negative makes it not work. On Jul 16, 8:04*pm, "Ashish Mathur" wrote: =if(A4-"*Microsoft*","Rolling deposit","") -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "zeetoe04" wrote in message ... Hi everyone, I'm trying to link these two sheets together as far as making them easier to integrate. I have this main sheet thats full of every group that will be coming to our establishment in the next 20 years. These groups are named for example "Microsoft Oct 09", "Microsoft Dec 11", etc - From a VLookup it pulls if they have a deposit or not from a hidden area - From an IF formula, it pulls if it is a certain sales managers initials and returns "SOCIAL RMS". I want to integrate a third formula to do the following: I have a small list (about 10 or so) of company names only - So it says "Microsoft". These are deposits that stay on file in a separate area of our accounting team. I want add a column with the formula that says "If the group name contains the word Microsoft, then say rolling deposit". What I'm having difficulty with is getting it to pull it if it CONTAINS the word. Please let me know if you have any suggestions for enhancing my sheet. BACKGROUND ON SHEET: COLUMNS: Group code, Group name, Sales person, (1) VLOOKUP to see if deposit on file in alternate hidden area, (2) IF ERROR in prev column, (3) IF prev col = true return 0 if false return deposit amount, and currently my last column (the only one unhidden since the sales person) is an (4) if the salesperson is MVW return value of social rms- if not return deposit amount from col 3 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Formula When Contains Partial Word
One way is to use Countif, as in:
=if(countif(a4,"*microsoft*")0,"rolling deposit","something else") You can also check more than one cell with Countif Regards Fred "zeetoe04" wrote in message ... Hi everyone, I'm trying to link these two sheets together as far as making them easier to integrate. I have this main sheet thats full of every group that will be coming to our establishment in the next 20 years. These groups are named for example "Microsoft Oct 09", "Microsoft Dec 11", etc - From a VLookup it pulls if they have a deposit or not from a hidden area - From an IF formula, it pulls if it is a certain sales managers initials and returns "SOCIAL RMS". I want to integrate a third formula to do the following: I have a small list (about 10 or so) of company names only - So it says "Microsoft". These are deposits that stay on file in a separate area of our accounting team. I want add a column with the formula that says "If the group name contains the word Microsoft, then say rolling deposit". What I'm having difficulty with is getting it to pull it if it CONTAINS the word. Please let me know if you have any suggestions for enhancing my sheet. BACKGROUND ON SHEET: COLUMNS: Group code, Group name, Sales person, (1) VLOOKUP to see if deposit on file in alternate hidden area, (2) IF ERROR in prev column, (3) IF prev col = true return 0 if false return deposit amount, and currently my last column (the only one unhidden since the sales person) is an (4) if the salesperson is MVW return value of social rms- if not return deposit amount from col 3 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Formula When Contains Partial Word
Hi,
yes that is the = sign. Sorry about that. try if(A4="Microsoft*","Rolling deposit","") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Ashish Mathur" wrote in message ... =if(A4-"*Microsoft*","Rolling deposit","") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "zeetoe04" wrote in message ... Hi everyone, I'm trying to link these two sheets together as far as making them easier to integrate. I have this main sheet thats full of every group that will be coming to our establishment in the next 20 years. These groups are named for example "Microsoft Oct 09", "Microsoft Dec 11", etc - From a VLookup it pulls if they have a deposit or not from a hidden area - From an IF formula, it pulls if it is a certain sales managers initials and returns "SOCIAL RMS". I want to integrate a third formula to do the following: I have a small list (about 10 or so) of company names only - So it says "Microsoft". These are deposits that stay on file in a separate area of our accounting team. I want add a column with the formula that says "If the group name contains the word Microsoft, then say rolling deposit". What I'm having difficulty with is getting it to pull it if it CONTAINS the word. Please let me know if you have any suggestions for enhancing my sheet. BACKGROUND ON SHEET: COLUMNS: Group code, Group name, Sales person, (1) VLOOKUP to see if deposit on file in alternate hidden area, (2) IF ERROR in prev column, (3) IF prev col = true return 0 if false return deposit amount, and currently my last column (the only one unhidden since the sales person) is an (4) if the salesperson is MVW return value of social rms- if not return deposit amount from col 3 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Formula When Contains Partial Word
Thanks for the help! The CountIf worked, not even sure how but it
worked! :) The second one still didn't work :( But if I did the first with only one asterisk it worked. Thanks again!! On Jul 17, 7:55*pm, "Ashish Mathur" wrote: Hi, yes that is the = sign. *Sorry about that. *try if(A4="Microsoft*","Rolling deposit","") -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "Ashish Mathur" wrote in message ... =if(A4-"*Microsoft*","Rolling deposit","") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "zeetoe04" wrote in message ... Hi everyone, I'm trying to link these two sheets together as far as making them easier to integrate. I have this main sheet thats full of every group that will be coming to our establishment in the next 20 years. These groups are named for example "Microsoft Oct 09", "Microsoft Dec 11", etc - From a VLookup it pulls if they have a deposit or not from a hidden area - From an IF formula, it pulls if it is a certain sales managers initials and returns "SOCIAL RMS". I want to integrate a third formula to do the following: I have a small list (about 10 or so) of company names only - So it says "Microsoft". These are deposits that stay on file in a separate area of our accounting team. I want add a column with the formula that says "If the group name contains the word Microsoft, then say rolling deposit". What I'm having difficulty with is getting it to pull it if it CONTAINS the word. Please let me know if you have any suggestions for enhancing my sheet. BACKGROUND ON SHEET: COLUMNS: Group code, Group name, Sales person, (1) VLOOKUP to see if deposit on file in alternate hidden area, (2) IF ERROR in prev column, (3) IF prev col = true return 0 if false return deposit amount, and currently my last column (the only one unhidden since the sales person) is an (4) if the salesperson is MVW return value of social rms- if not return deposit amount from col 3- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT - only partial variation to formula | Excel Worksheet Functions | |||
partial word match for conditional formatting | Excel Worksheet Functions | |||
Vlookup using partial word, first 4 letters | Excel Worksheet Functions | |||
partial calculation displays in formula | Excel Discussion (Misc queries) | |||
Formula to calculate partial payments | Excel Worksheet Functions |