where to begin

abanky · Apr 24th, 2002, 06:20 AM

Here is my problem,, I have a workbook with 4 sheets, sheet one contains a list of people andsecurity numbers that may or may not be on the other sheets, sheet two contains a list of names and security # for company one, sheet three contains a list of names and security # for company two and sheet four contains a list of names and security # for company three.

here is what i want to do, I want to take the people on sheet one and find out which company a person works for, by security #, if not are they listed on sheet two, are they listed on sheet three, or sheet four.

I don't know how vba works right now, so i am kind of looking for a if then formula, any help would be great.

Abanky

jrnyman · Apr 24th, 2002, 07:02 AM

I would try a formula with IF, ISNA, and VLOOKUP statements. If all the codes are in Column A and names in Column B try something like:

=IF((ISNA(VLOOKUP(A1,Sheet2!$A$1:$Z$2,2,FALSE))=FALSE),"CompanyOne",IF((ISNA(VLOOKUP(A1,Sheet3!$A$1:$Z$2,2,FALSE))=FALSE),"CompanyTwo",IF((ISNA(VLOOKUP(A1,Sheet4!$A$1:$Z$26,2,FALSE))=FALSE),"CompanyThree","UNEMPLOYED")))

abanky · Apr 24th, 2002, 07:13 AM

Thank you, that worked perfect,

Abanky

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 24th, 2002, 06:20 AM	#1
abanky Board Regular Join Date: Apr 2002 Posts: 80	Here is my problem,, I have a workbook with 4 sheets, sheet one contains a list of people andsecurity numbers that may or may not be on the other sheets, sheet two contains a list of names and security # for company one, sheet three contains a list of names and security # for company two and sheet four contains a list of names and security # for company three. here is what i want to do, I want to take the people on sheet one and find out which company a person works for, by security #, if not are they listed on sheet two, are they listed on sheet three, or sheet four. I don't know how vba works right now, so i am kind of looking for a if then formula, any help would be great. Abanky

Apr 24th, 2002, 07:02 AM	#2
jrnyman Board Regular Join Date: Mar 2002 Location: Boston, MA Posts: 105	I would try a formula with IF, ISNA, and VLOOKUP statements. If all the codes are in Column A and names in Column B try something like: =IF((ISNA(VLOOKUP(A1,Sheet2!$A$1:$Z$2,2,FALSE))=FALSE),"CompanyOne",IF((ISNA(VLOOKUP(A1,Sheet3!$A$1:$Z$2,2,FALSE))=FALSE),"CompanyTwo",IF((ISNA(VLOOKUP(A1,Sheet4!$A$1:$Z$26,2,FALSE))=FALSE),"CompanyThree","UNEMPLOYED")))

Apr 24th, 2002, 07:13 AM	#3
abanky Board Regular Join Date: Apr 2002 Posts: 80	Thank you, that worked perfect, Abanky