I hope I made myself somewhat clear,
|Check out our Excel Resources|
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
'Uses Vlookup across ALL sheets until a match is found
Dim Wsht As Worksheet
On Error Resume Next
For Each Wsht In ActiveWorkbook.Worksheets
FindIt = WorksheetFunction.VLookup(What, Sheets(.Index).Range(Where.Address), Colnum, TorF)
If FindIt <> "" Then Exit For
LookAcrossSheets = FindIt
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.
This will look in all sheets until it finds 2564 in D1:D1000 and return the value in the same row in Column E
OzGrid Business Applications
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
Is it possible, even with your code?
This might be also of interest to you.
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!
This might be also of interest to you. 11664.html Aladin
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?
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,
I want to do something like:
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)
If A5 contains a range as value like $C$4:$F$16 or a name referring to a range, then you can use