Lookup sheet name

[ArcherSteve]

Hey guys,
I have a datafile being sent to me about once a week with various names in 5 different work sheets (4 sheets are individual categories and the last is all the data, which are people's names, on the one sheet)...(btw, not my choice....). Now what I need to do is have excel look at the person's name and tell me what sheet it is coming from and spit that into a cell. Is this at all possible? If so, how?


I've searched the forums for a while and havent found anything yet, so if I missed something, please point it out.

Thank you in advance for any help

-Steve

 

[shades]

Howdy. It depends on how the data is arranged. Sometimes an INDEX/MATCH combination works well, and is more flexible than VLOOKUP.

Can you provide a small "sanitized" version with at least two pages?
________
VAPORIZER MANUFACTURERS

 

[ArcherSteve]

Thanks for the reply shades. I've attached a copy of the data with last names and addresses removed. The first sheet, which is named "All', has all the people in it. The following 4 sheets have the people in the correct sheet (sorted by division). What I need to do is on the first sheet, have excel look for the name in the other sheets and tell me what sheet it is on. Now I know its not that much data right now, but it will grow in a couple weeks.


Thanks again.

-steve

 

[HaHoBe]

Hi, steve,

try this one as an idea starting with cell A3 and drag the formula down (additional breaks added but not necessary in the formula):

=IF(NOT(ISERROR(VLOOKUP(A3,'Female Compound'!A:A,1,0))),"Female Compund",
IF(NOT(ISERROR(VLOOKUP(A3,'Female Recurve'!A:A,1,0))),"Female Recurve",
IF(NOT(ISERROR(VLOOKUP(A3,'Male Recurve'!A:A,1,0))),"Male Recurve",
IF(NOT(ISERROR(VLOOKUP(A3,'Male Compound'!A:A,1,0))),"Male Compund",""))))

Ciao,
Holger

 

[ArcherSteve]

Holger, wow. That works almost perfectly. There is just one slight problem, there a few father and sons in the data, who are in different divisions. Is there anyway to modify that code so that it looks at both "Last Name" and "First Name" columns?

I've attached a new data file that has both last name and first name fields (data will be published on a website anyway) to help show what I need.

Thank you again for your help.

 

[HaHoBe]

Hi, Steve,

you may concatenate the names into one cell (= B3 & ", " & A3) in all sheets and then use VLookup. I would try and work around the John Doe problem by using another column with the personal number of each employee (or create one like 060506hb for somebody born today with first name Holger and last name B...). If you use the latter approach please make sure to either use values or text exclusively - if you mix them up VLookup will fail to deliver the correct values.

Ciao,
Holger

 

[ArcherSteve]

Holger, thanks again for your help, I really do appreciate it. I was toying around with the idea of concatenating but I really didn't want to. I guess that would be the easiest solution. Thanks again for the help.