Vlookup formula that returns multiple answers by searching through multiple worksheets in a single workbook

[kfromtexas]

Hi,

I am trying to determine if I am using the correct type of formula and figure out why I am getting an error with the formula I am using (maybe I should use a different formula completely.. such as an index or some sort of array or a if... IDK).

I need to search column C row 1:556, on sheets 3 through 80 in a workbook for a zip code, and then return the name of an individual in column D(once again row 1:56) that is assigned to work that zip code. ALL the names of the individuals listed then need to appear onSheet1 and can be listed vertically from A8:A80.

The purpose is a I need to enter in a zip code on sheet 1 and have a list ofall employees who work in that zip code return.

Sheets 3-80 employee data sheets listing every zip code that employee works inand all the employee skill sets. Each Employee works in multiple zip codes andmany employees work in the same zip codes. Our schedule needs to determinewhich employee to send to what location based on what zip code the location isin and what skill set is needed for the visit.

This is to manage our nurses visiting patients... LOl helping me will behelping save lives!!! LOL But seriously to ensure patients can all be seen ontime to receive the best possible care needed we need a better way of thisbeing organized! So THANK YOU THANK YOU IN ADVANCE!

I was trying to use a vlookup function but because it is across multiple sheetsand will produce multiple answers I am thinking maybe it needs to be a morecomplexed formula OR a completely different formula all together.

I had: =VLOOKUP(A3,'Employee 1'!C2:D30,2,TRUE)
And this works perfect to search 1 employee record problem is I need thissearch done between sheets 3-80

So then I tried this:=VLOOKUP(A3,'Employee 1':'Employee 6'!C2:D556,2,TRUE)
And I get an error: #name ?

*****Please note, we have 80 employees total, I am trying to test formulas ononly 6 employees right now. and only have 8 sheets total in the work book.*****
I need all the results of the employees names to then return on sheet 1 andlist vertically from cell A8 down.

ANY ADVICE!?! Am I even using the correct formula? I am self teaching googlingand youtubing tutorials to try and figure this out. I have been working on thisfor over a week even at home and on the weekend (and am now and starting tothink my managers in the office think I am just playing around watchingYoutube! lol) but this is a SERIOUS and crucial need here at our office. ANYhelp is sincerely appreciated!!!

 

[mumps]

Click here to download your file.
I have made a few changes. You had lists of zip codes and skill sets at the bottom of the "Search" sheet. These lists interfered with the macro so I deleted them and I renamed the second sheet and placed the skill sets in column D. I then used column A to populate the drop down in A3 and column D to populate the drop down in E3. These drop downs are now dynamic which means that if you add or delete zip codes or skills sets in the "Zip Code-City-Skill Set" sheet, the drop downs will adjust automatically. Please don't change the name of the "Zip Code-City-Skill Set" sheet as this will affect the data validation formula.

 

[kfromtexas]

Hey Mumps,

It worked!!!!!

Question: Everytime we add a new employee (so add an additional sheet) will it automatically work or will I need to update the Macro? Sam ething if we change anythign on the employee sheet will it interfere with the macro or if we gain new terrritory and need to enter new zip codes onto the list or zip codes & cities?

Lastly, is there any way we can thank you!?!? My manager wanted to take you out for lunch haha... tol dher that may be a challenge as it appears you live in a different country. Thank you so very very much fo ryour help!! Please let us know if there is anythign we can do an dI will talk with my manager about it!

 

[mumps]

You are most welcome and thank your manager for her generous offer. That is thanks enough!!
You don't have to make any changes to the macro. It will take care of all the issues you raised as long as you don't change the way the data is organized on the sheets. You can add sheets for new employees as long as you continue to name the sheets in the same fashion. You can also continue to add zip codes and cities.

 

[kfromtexas]

Okay thank you so much!!!

 

[mumps]

My pleasure.