Vlookup to reference another Workbook

Jbutler6

New Member
Joined
May 4, 2012
Messages
7
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
365
Platform
Windows, Mobile
Hi,
What version of Excel are you using?
Have you searched the forum? link.
Have you looked at the Excel help about vlookup?
 
Last edited:

Jbutler6

New Member
Joined
May 4, 2012
Messages
7
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
365
Platform
Windows, Mobile
Ok, noted.

have you tried this?
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,396
Messages
5,414,183
Members
403,517
Latest member
richardthevbanoob

This Week's Hot Topics

Top