Archive of Mr Excel Message Board

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

Dave,
Thanks! I'll try this out and report back
whether or not I can do it (I'm rather dumb ;) ).
Thanks again!
Ernesto

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

Ernesto

This might be also of interest to you.

11664.html

Aladin

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

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,

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,

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)

====================
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