Trouble with a VLOOKUP formula

[WhytheQ]

Originally the formula was:
=VLOOKUP(A3,'I:\Fld\[Fil 01 01 06.xls]Pricing'!$A$1:$D$30,4,FALSE)

But the date in the file name can change regularly and there are loads
of formula like the above, so I thought to be able to change a date in
single cell C1 and the formulas to dynamically change would be good.
I've tried the 2 below formula without any success:

1.=VLOOKUP(A3,'I:\Fld\[Fil " & C1 &".xls]Pricing'!$A$1:$D$30,4,FALSE)
2.=VLOOKUP(A3,'I:\Fld\[Fil " & INDIRECT(C1)
&".xls]Pricing'!$A$1:$D$30,4,FALSE)

Any one got any ideas??
Help greatly appreciated.

Jason

 

[Vass]

Originally the formula was:
=VLOOKUP(A3,'I:\Fld\[Fil 01 01 06.xls]Pricing'!$A$1:$D$30,4,FALSE)

But the date in the file name can change regularly and there are loads
of formula like the above, so I thought to be able to change a date in
single cell C1 and the formulas to dynamically change would be good.
I've tried the 2 below formula without any success:

1.=VLOOKUP(A3,'I:\Fld\[Fil " & C1 &".xls]Pricing'!$A$1:$D$30,4,FALSE)

the concantenating &'s sit outside the "quotes" as will the cell reference
+C1

 

[WhytheQ]

Thanks for the help Vass.
Where do the "quotes" go then? If I do the below, and just get rid of
them altogether, then things still don't work!

i.e.=VLOOKUP(A3,'I:\Fld\[Fil & C1 & .xls]Pricing'!A130,4,FALSE)

........also if I just put the &'s outside the quotes, like below, then
things are still not working:

i.e.=VLOOKUP(A3,'I:\Fld\[Fil & "C1" & .xls]Pricing'!A130,4,FALSE)

I don't find setting this formula up intuitive at all, so any more help
would be much appreciated.
Thanks
Jason

 

[Pete_UK]

I think what you want is:

..=VLOOKUP(A3,INDIRECT("'I:\Fld\[Fil"&C1&".xls]Pricing'!A130"),4,FALSE)

Basically, the inner part of the formula is built up as

"'I:\Fld\[Fil" & C1 & ".xls]Pricing'!A130"

i.e. you are joining three strings together - the first and last are
literal strings so must be enclosed in quotes, whereas the middle
string is what is contained in cell C1. The INDIRECT( ) function allows
you to combine addresses in this way.

Hope this helps.

Pete

 

[Pete_UK]

Actually, instead of just C1 I think you will need to convert this into
a specific format using the TEXT( ) function, as follows:

=VLOOKUP(A3,INDIRECT("'I:\Fld\[Fil"&TEXT(C1,"dd mm
yy")&".xls]Pricing'!A130"),4,FALSE)

It's not clear if you use "dd mm yy" or "mm dd yy" format in your
filenames - adjust to suit.

Hope this helps.

Pete

 

[Dave Peterson]

And =indirect() won't work if that other workbook is closed.

And if the workbook is open, the drive\folder stuff could be dropped.

I think what you want is:

.=VLOOKUP(A3,INDIRECT("'I:\Fld\[Fil"&C1&".xls]Pricing'!A130"),4,FALSE)

Basically, the inner part of the formula is built up as

"'I:\Fld\[Fil" & C1 & ".xls]Pricing'!A130"

i.e. you are joining three strings together - the first and last are
literal strings so must be enclosed in quotes, whereas the middle
string is what is contained in cell C1. The INDIRECT( ) function allows
you to combine addresses in this way.

Hope this helps.

Pete

 

[Pete_UK]

Good points, Dave. I forgot!

Pete

 

[WhytheQ]

thanks for all the help lads.....even though Dave had to go and "p**s
on my fire" a bit at the end there !!
the other workbooks will be shut, so atleast I know that I need to find
an alternative route - think I'll muck around with a bit of VBA

thanks again
Jason

 

[Dave Peterson]

You could use a helper worksheet and a macro that populates that sheets with
formulas that point at that closed workbook--rebuild the formulas when the
workbook name would change.

Then use your =vlookup() against that helper sheet's range.

 

[vane0326]

You could try a worksheet event code.
You said the the date is in cell C1 so you save your files by date. Now
right click at the bottom of the worksheet tab go to view code then
paste this code in the white area. Now anytime you enter the file name
in cell C1 it will open and close that file but you will still retain
your vaules. Try it out see if it works for you. But you need to have
the INDIRECT formula.

=VLOOKUP(A3,INDIRECT("'I:\Fld\[Fil"&C1&".xls]Pricing'!A130"),4,FALSE)



Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C2")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
ActiveSheet.EnableCalculation = True
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

With Application.FileSearch
.NewSearch
.LookIn = "C:\" ' Change the file path to your suite
.Filename = [A2] & ".XLS"

If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then
Workbooks.OpenText .FoundFiles(1), xlWindows
ActiveWorkbook.Close

End If
End With

ActiveSheet.EnableCalculation = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub