Vlookup to reference another Workbook

[Jbutler6]

Hi all,

I am hoping y'all can help me with something. I have several large (big enough to not make it practical to combine them) workbooks in 1 folder on a shared server.

I have created a seperate workbook to pull data from these workbooks based on a "customer number" criteria. What i would like to happen, is to type in this reference number and based on a key have the data for that particular customer draw from the various sheets.


IE:


Agent Workbook 1
Customer A
Customer B
Customer C

Agent Workbook 2
Customer D
Customer E
Customer F

Can i use vlookup (open to other suggestions) to input "Customer A" and it knows to reference Agent Workbook 1. Each of the agent workbooks are identical in formatting, i just need some help with the sheet knowing it needs to change Agent Workbooks based on the Customer

Thanks for any help yall can provide!

-J

 

[cyrilbrd]

Hi,
What version of Excel are you using?
Have you searched the forum? link.
Have you looked at the Excel help about vlookup?

 

[Jbutler6]

Thanks for the reply!

Hi,
What version of Excel are you using?
Have you searched the forum? link.
Have you looked at the Excel help about vlookup?

I am using Excel 2010

I checked out the forums and found several great examples about how to search across multiple workbooks using an array:

http://www.mrexcel.com/forum/excel-questions/648831-hyperlinked-vlookup.html
http://www.mrexcel.com/forum/excel-questions/648831-hyperlinked-vlookup.html

Unfortuantely, i am not sure if either of these solutions will work. I am looking across 50+ workbooks, and i believe you are limited to 20 (qty?) seeded statements in a formula.

I also checked out the Help area, but as usual the program is not very helpful for complex uses of formulas.

The large quantity of workbooks that i am looking across makes me think that there has to be a way to refernce specifically which hyperlinked workbook the data range exists in. It is not an issue for the source workbook to be open while the values update. While i would like to avoid it, i am open to possible vba solutions as well.

Thanks

-J

 

[cyrilbrd]

Ok, noted.

have you tried this?

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num, Range_look)
'Written by OzGrid.com
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Use VLOOKUP to Look across ALL Worksheets and stops _
 at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound
On Error Resume Next
For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = _
WorksheetFunction.VLookup _
        (Look_Value, Tble_Array _
        , Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet
Set Tble_Array = Nothing
VLOOKAllSheets = vFound
End Function

This is an UDF

you might want to have a look at this as well: link.
Those may not be immediate solution but they might be helpful or open doors to new possibilities.
Keep us posted.