Lookup formula

[Shona]

Hi

Not sure if this is possible in this formula

=VLOOKUP(B8,'7716130124.xls'!$B$4:$H$4,7,0)

Is it possible to say instead of the file name 7716130124.xls look up what
ever is typed in a cell which would be E3 which would be a file name they
want to be able to look up column 7.

Any ideas?

Cheers Shona

[Jason Morin]

Try:

=VLOOKUP(B8,INDIRECT("'"&E3&".xls'!$B$4:$H$4"),7,0)

The workbook to which you refer in the formula must be
open.

HTH
Jason
Atlanta, GA

>.
>

[Shona]

Thank you shame you have to have it open though

Thanks again Shona

"Jason Morin" <jason...@us.exel.com> wrote in message
news:096f01c35ce8$48bc3d90$a601...@phx.gbl...

[Harlan Grove]

"Shona" wrote...

>Thank you shame you have to have it open though

..

You don't, but it requires VBA to dereference dynamic files or ranges in closed
workbooks.

'----- begin VBA -----
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
'----- end VBA -----

Use this as

=VLOOKUP(B8,pull("'"&INFO("Directory")&"7716130124.xls"&"'!$B$4:$H$4"),7,0)

if the file would be located in Excel's current working directory (the one in
which Open or Save As dialogs start off). If these files would be in a different
directory, use that directory's full pathname in place of INFO(..). Replace
"7716130124.xls" with a reference to or a formula for the filenanme.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.