Vlookup on multiple sheets

[roofi]

Hello, I am Abdul Rouf Roofi, Document Controller from Descon Engineering Limited Pakistan. I came to this forum through google and i would appreciate if someone help me to use Vlookup on multiple sheets. I am attaching my worksheet here for kind perusal.

Thanks

[FDibbins]

Hi and welcome to the forum

Change your formula to this...
=VLOOKUP(G$5,INDIRECT(LEFT(G5,2)&"!D2:Q10"),2,0)

[savannaexcel]

Great formula. Mine are loooong ones.

[roofi]

Dear Savannaexcel,
Thank you very much. your formulae worked.but only for 7 sheets
my 50% problem solved.

[roofi]

Dear FDibbins,
yours is Great it really works............Thanks a lot, and thanks for explaining syntax.
100% solved

[savannaexcel]

Enter the following formulas in each of the three cells:

=+IFERROR(VLOOKUP(G$5,RR!D:Q,2,0),IFERROR(VLOOKUP(G$5,PF!D:Q,2,0),IFERROR(VLOOKUP(G$5,SA!D:Q,2,0),IFERROR(VLOOKUP(G$5,SI!D:Q,2,0),IFERROR(VLOOKUP(G$5,MR!D:Q,2,0),IFERROR(VLOOKUP(G$5,FB!D:Q,2,0),"Could Not Be Found"
))))))


=+IFERROR(VLOOKUP(G$5,RR!D:Q,3,0),IFERROR(VLOOKUP(G$5,PF!D:Q,3,0),IFERROR(VLOOKUP(G$5,SA!D:Q,3,0),IFERROR(VLOOKUP(G$5,SI!D:Q,3,0),IFERROR(VLOOKUP(G$5,MR!D:Q,3,0),IFERROR(VLOOKUP(G$5,FB!D:Q,3,0),"Could Not Be Found"
))))))


=+IFERROR(VLOOKUP(G$5,RR!D:Q,10,0),IFERROR(VLOOKUP(G$5,PF!D:Q,10,0),IFERROR(VLOOKUP(G$5,SA!D:Q,10,0),IFERROR(VLOOKUP(G$5,SI!D:Q,10,0),IFERROR(VLOOKUP(G$5,MR!D:Q,10,0),IFERROR(VLOOKUP(G$5,FB!D:Q,10,0),"Could Not Be Found"
))))))

[FDibbins]

Thanks. The indirect() function is ideal for this sort of thing

[savannaexcel]

And what would be the best alternative for an indirect function between two workbooks? I have been using the code below but it is slow.

Function pull(xref As String) As Variant
'inspired by Bob Phillips and Laurent Longre
'but written by Harlan Grove
'-----------------------------------------------------------------
'Copyright (c) 2003 Harlan Grove.
'
'This code is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published
'by the Free Software Foundation; either version 2 of the License,
'or (at your option) any later version.
'-----------------------------------------------------------------
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, c As Range, n As Long

pull = Evaluate(xref)

If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp 'immediate clean-up at this point

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro

On Error Resume Next 'now clean-up can wait

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)
Else
For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c

pull = r.Value
End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing
End If
End Function

[FDibbins]

savanna, please use code tags when posting VBA (see my note 1 below).

Also, indirect() doesnt work between workbooks/files unless bot are open, you need an add-in for that (more_func i think its called)

roofi, the indirect() function is a means of taking text, and converting into something that excel can use as a range. So...
=VLOOKUP(G$5,INDIRECT(LEFT(G5,2)&"!D2:Q10"),2,0)
is taking the left 2 characters of the contents of G5 (FB or MR etc), and then combing that with your range D2:Q10 to give you the sheetname and cell range FB!D2:Q10. You can add as many sheets as you want, as long as you keep to 2 characters for the name. If you want to use more, then you can change the
LEFT(G5,2)
to LEFT(G5,search("=",G5,1)-1)
That will allow for more than 2 characters

[roofi]

Dear FDibbins,
yours is Great it really works............Thanks a lot, and thanks for explaining syntax.
100% solved