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]

I think that you will need a macro to do what you want. When you say that you need to enter a zip code on sheet 1, in which cell in Sheet1 would you enter the zip code?

 

[kfromtexas]

Hi,
I have a data validation in cell A3 that contains every single zip code in every single county that our company covers. So an individual can either type in the zip code or use the drop down to locate the zip code then I want it to search the sheet 3:80 in the workbook in column C to then pull the name to the right of this column, D, and have this search performed on all pages 3:80 to then list all the employee names on sheet 1 in columnA starting in cell A8.

I don't know anything about Macro or even what that is. :/

So I may have to do a lot more research. I didn't think it would be this complicated.

ANY help is VERY appreciated. I don't mind reading, researching watching videos etc. But I just don't even know what to search for at this moment to get me on the correct path.

 

[kfromtexas]

The date validation with the zip code in cell A3 is also located on sheet 1.

 

[mumps]

Copy and paste the macro below into the worksheet code module. Do the following: right click the tab name for Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in the drop down in cell A3.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim x As Long, bottomA As Long, fnd As Range
    For x = 3 To 80
        Set fnd = Sheets(x).Range("C:C").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            bottomA = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If bottomA < 8 Then
                Range("A8") = fnd.Offset(0, 1)
            Else
                Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = fnd.Offset(0, 1)
            End If
        End If
    Next x
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[kfromtexas]

Hey Mumps, Thanks for the stab there but that actually didn't do anything?

Should I have the formulas I had in cell A8 deleted first?

 

[mumps]

The macro would have overwritten the formulas because they aren't necessary. I tried the macro on some dummy data and it worked properly. How are your sheets 3 to 80 named? Are they "Sheet3", "Sheet4", "Sheet5", etc. ?

 

[kfromtexas]

Hey Mumps,

That may have soemthing to do with it...
Sheet1 (Search)
Sheet10 (Employee 8)
Sheet11 (Employee 9)
Sheet12 (Employee 10)
Sheet13 (Employee 11)
Sheet14 (Employee 12)
Sheet15 (Employee 13)
Sheet16 (Employee 14)
Sheet17 (Employee 15)
Sheet18 (Employee 16)
Sheet19 (Employee 17)
Sheet2 (Zip Code & City Data Sheet)
Sheet20 (Employee 18)
Sheet3 (Employee 1)
Sheet4 (Employee 2)
Sheet5 (Employee 3)
Sheet6 (Employee 4)
Sheet7 (Employee 5)
Sheet8 (Employee 6)
Sheet9 ( Employee 7)

That is the way it is shown (same order) in the window on the left hand side in the Project - VBAProject window where you told me to go to get to the screen to copy and paste the macro.
Also I am using Excel 365 if that helps.

I wish I could upload the workbook some kind of way or put a screen shot in here... :/

I was thinking it would be some sort of formula and that I could then tweak it because on the same sheet that the zip code is entered on to search to see what employees work in that zip code I also have another data validation drop down box that contains different skill sets. I need to be able to do the same thing and select the appropriate skill set and then have any employee that has that skill set populate below so they can make sure they are assigning the correct nurses that work in that zip code and can perform the task the patient needs to then go to the patients home. So... in cell E3 is a validation drop down containing skill sets. Select a skill set and then it fills the employee name in cell E8. Like I said I hav eit working for Employee 1 (which is sheet3). So in cell E8 I have: =vlookup(E3,'Employee 1'!A2:b15,2,True)

Also on this page I have in cell A4 =vlookup(A3,' Sip Code & City Data Sheet'!A2:B556,2,) so that way when they select or type in a zip code the cell below populate the corresponding city.

Some in employees work in about 500 different zip codes, others maybe only 2-3 and some employees only have maybe 5 skillsets where others have 12. I have the employee “data sheets” formatted odd because I was trying to get the vlookup to work so maybe I need to have the employee data sheets formatted differently???


I don't know if any of this helps... Let me know if you need more information.

Also each employee sheet I have it formatted the following way:

Skill Set	Name:	Zip Code	Name:	Cities	Name:
Infuse PICC	Alyssa Newman, RN	76065	Alyssa Newman, RN	MIDLOTHIAN	Alyssa Newman, RN
Labs	Alyssa Newman, RN	76084	Alyssa Newman, RN	VENUS	Alyssa Newman, RN
Pull	Alyssa Newman, RN	75167	Alyssa Newman, RN	WAXAHACHIE	Alyssa Newman, RN
PICC dsg	Alyssa Newman, RN	76064	Alyssa Newman, RN	MAYPEARL	Alyssa Newman, RN
Hosp Hookup	Alyssa Newman, RN	76651	Alyssa Newman, RN	ITALY	Alyssa Newman, RN
Medi Port	Alyssa Newman, RN	76061	Alyssa Newman, RN	LILLIAN	Alyssa Newman, RN
Central Line	Alyssa Newman, RN	75165	Alyssa Newman, RN	Waxahachie	Alyssa Newman, RN
Pluerex Cath	Alyssa Newman, RN	75154	Alyssa Newman, RN	Red Oak	Alyssa Newman, RN
Wound Vac	Alyssa Newman, RN	75152	Alyssa Newman, RN	Palmer	Alyssa Newman, RN
Wound Care	Alyssa Newman, RN	75146	Alyssa Newman, RN	Lancaster	Alyssa Newman, RN
IVIG	Alyssa Newman, RN	75115	Alyssa Newman, RN	Desoto	Alyssa Newman, RN
Remicaid	Alyssa Newman, RN	75134	Alyssa Newman, RN	Lancaster	Alyssa Newman, RN
TPN	Alyssa Newman, RN	75104	Alyssa Newman, RN	Cedar Hill	Alyssa Newman, RN
Chemo Take Down	Alyssa Newman, RN	75137	Alyssa Newman, RN	Duncanville	Alyssa Newman, RN
		75249	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75116	Alyssa Newman, RN	Duncanville	Alyssa Newman, RN
		75236	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75233	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75237	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75232	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75241	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75216	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75224	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75203	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75208	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75211	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75202	Alyssa Newman, RN	Dallas	Alyssa Newman, RN
		75215	Alyssa Newman, RN	Dallas	Alyssa Newman, RN

<tbody>
</tbody>

 

[mumps]

So the sheet names you want to search all have the word "Employee" in them. Is this correct? The problem with the vlookup formula is that it can search only one sheet. To search through all of the sheets, you need a macro. This site doesn't allow you to attach files. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data. Explain in detail what you want to do with the zip codes skill sets. It would help if you could do a manual mockup of what the end result should look like.

 

[kfromtexas]

Hi Mumps,

I created a link in drop box. Let me know if this works.
https://www.dropbox.com/s/zlac6ra9brr4iqi/MUMPS_TEST.xlsx?dl=0

Below is a link containing the same workbook, but the front page has an example of how I want it to populate the search results. So on on epage they can search either using a zip code or using a skill set to see which employees posses that skill set or work in that zip code. If it is easier for me to have a single search on a single sheet, I can do that as well. Also, if the sheets need to be labeled differently I can change that as well, same thing with the way I have the employee sheets formatted.

https://www.dropbox.com/s/fze1afw4114j0p4/MUMPS_TEST_%20example%20of%20search%20sheet.xlsx?dl=0

Thank you so much! I am EXTREMELY appreciative of your help. please let me know if there is anything I can do to help or if you need me to research things or do any work on my own. I am totally fien with it. I don't want this to consume you in any way or hav eyou do all the work but I am uncertain what direction to turn. ​