Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Vlookup and Multiple Worksheets

Posted by Ernesto on April 16, 2001 9:29 PM
I have a huge workbook, which has 9 worksheets. Each worksheet has the same type of data, An Account #, Name, Adress, City, State, Zip.
I need to be able to use vlookup by referencing the
account #, but the account # might be on any of the 9 worksheets. I have the formula where I can get the right information when I tell excel exactly in which worksheet to look in, but can I somehow have excel look through all the worksheets by putting something in
the formula?

I hope I made myself somewhat clear,
Thanks
Ernesto


Check out our Excel Resources

Re: Vlookup and Multiple Worksheets

Posted by Dave Hawley on April 16, 2001 10:50 PM

Hi Ernesto

I would use a Custom function (UDF) for this. the one below will use the VLOOKUP across ALL sheets until it finds a match.

To use it Push Alt+F11 and go to Insert>Module and paste in the code below:


Function LookAcrossSheets(What, Where As Range, _
Colnum As Integer, TorF As Boolean)
'Written by OzGrid Business Applications
'www.ozgrid.com
'Uses Vlookup across ALL sheets until a match is found

Dim Wsht As Worksheet
Dim FindIt

On Error Resume Next
For Each Wsht In ActiveWorkbook.Worksheets
With Wsht
FindIt = WorksheetFunction.VLookup(What, Sheets(.Index).Range(Where.Address), Colnum, TorF)
End With

If FindIt <> "" Then Exit For
Next Wsht

LookAcrossSheets = FindIt

End Function

Push Alt+Q to return to Excel.
Push Shift+F3, scroll down to "User defined" then select "LookAcrossSheets". Use the fuction in the same way you would a normal VLOOKUP.


=LookAcrossSheets(2564,D1:F1000,2,FALSE)


This will look in all sheets until it finds 2564 in D1:D1000 and return the value in the same row in Column E


Dave

OzGrid Business Applications


Re: Vlookup and Multiple Worksheets

Posted by Ernesto on April 17, 2001 7:38 AM
Dave,
Thanks! I'll try this out and report back
whether or not I can do it (I'm rather dumb ;) ).
Thanks again!
Ernesto


Re: Vlookup and Multiple Worksheets

Posted by Ernesto on April 17, 2001 8:22 AM
Dave,
Thanks again for the help. I am trying your code right now and I am not getting results. I
think I need to explain what I have to do.
I have one workbook with 9 sheets. This workbook
contains names and addresses, account #'s, etc.
On another workbook, I only have account #'s.
What I need is to extract all the information from
the workbook that has all the info, and put it into the workbook that only contains the account
#'s.
When I used vlookup, I was able to use this:

=VLOOKUP(A1:A10,'[acq.xls]3'!A:G,2,FALSE)

Where vlookup will take the account numbers that
are in cells a1 - a10, use that number to search
in the acq.xls file, but I need to tell excel in
which sheet to look in. Since the account Number
can be in any of 9 sheets, I want a way to not
specify sheets.

Is it possible, even with your code?

Ernesto


Re: Vlookup and Multiple Worksheets

Posted by Aladin Akyurek on April 17, 2001 9:02 AM
Ernesto

This might be also of interest to you.

11664.html

Aladin


Re: Vlookup and Multiple Worksheets

Posted by Ernesto on April 17, 2001 9:28 AM
Aladin,
I looked at your post, and I believe I followed
everything until you created the TOT table and
then wrote this:
Now I create a lookup table of all my lookup tables on Sheet 1:

2 4 Table1
5 7 Table1

I name this range TOT.

I was lost there, let alone the formula. I tried
to follow it, but I didn't get it.
Can you clarify a bit more? Thanks!

Ernesto

This might be also of interest to you. 11664.html Aladin


Re: Vlookup and Multiple Worksheets

Posted by Aladin Akyurek on April 17, 2001 10:52 AM
Ernesto

BTW, "5 7 Table1" should be "5 7 Table2."

The idea I am thinkering about is that your account numbers may have a regular partitioning across your worksheets. If so, the proposal I referred to might work. I've just sent you the example file via e-mail.

Are your account numbers numeric or alphanumeric?

Aladin

============= Aladin,


Re: Vlookup and Multiple Worksheets

Posted by neitzl on April 17, 2001 11:07 AM
Aladin,
Yah, I figured it should be Table2.
My account #'s are alphanumeric, the alpha
part is in the beginning of the account # though,
so it does sort well.

Thanks for the email, I'll go checkfor it right
now! Ernesto BTW, "5 7 Table1" should be "5 7 Table2." The idea I am thinkering about is that your account numbers may have a regular partitioning across your worksheets. If so, the proposal I referred to might work. I've just sent you the example file via e-mail. Are your account numbers numeric or alphanumeric? Aladin ============= : Aladin,


Vlookup with Cell Reference in Place of Table

Posted by Marc on April 17, 2001 2:18 PM
Is it possible to refer to a cell rather than a specific table when using vlookup? For example, the typical vlookup might look like:

=vlookup(lookupValue,Table,Column)

I want to do something like:

=vlookup(lookupValue,A5,Column)

In this case, A5 might be equal to "Table"...Unfortunately when I try, the A5 comes back as a text and the formula fails (#N/A)


Re: Vlookup with Cell Reference in Place of Table

Posted by Aladin Akyurek on April 17, 2001 2:35 PM

====================
Marc

If A5 contains a range as value like $C$4:$F$16 or a name referring to a range, then you can use

=VLOOKUP(lookup-value,INDIRECT(A5),lookup-column,{0,1})

Aladin


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.