software

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 quot; amp; C1 amp;quot;.xls]Pricing'!$A$1:$D$30,4,FALSE)
2.=VLOOKUP(A3,'I:\Fld\[Fil quot; amp; INDIRECT(C1)
amp;quot;.xls]Pricing'!$A$1:$D$30,4,FALSE)

Any one got any ideas??
Help greatly appreciated.

Jasonquot;WhytheQquot; gt; wrote in message oups.com...
gt; Originally the formula was:
gt; =VLOOKUP(A3,'I:\Fld\[Fil 01 01 06.xls]Pricing'!$A$1:$D$30,4,FALSE)
gt;
gt; But the date in the file name can change regularly and there are loads
gt; of formula like the above, so I thought to be able to change a date in
gt; single cell C1 and the formulas to dynamically change would be good.
gt; I've tried the 2 below formula without any success:
gt;
gt; 1.=VLOOKUP(A3,'I:\Fld\[Fil quot; amp; C1 amp;quot;.xls]Pricing'!$A$1:$D$30,4,FALSE)

the concantenating amp;'s sit outside the quot;quotesquot; as will the cell reference
C1
Thanks for the help Vass.
Where do the quot;quotesquot; 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 amp; C1 amp; .xls]Pricing'!A130,4,FALSE)

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

i.e.=VLOOKUP(A3,'I:\Fld\[Fil amp; quot;C1quot; amp; .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
JasonI think what you want is:

..=VLOOKUP(A3,INDIRECT(quot;'I:\Fld\[Filquot;amp;C1amp;quot;.xls]Pricing'!A130quot;),4,FALSE)

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

quot;'I:\Fld\[Filquot; amp; C1 amp; quot;.xls]Pricing'!A130quot;

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.

PeteActually, 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(quot;'I:\Fld\[Filquot;amp;TEXT(C1,quot;dd mm
yyquot;)amp;quot;.xls]Pricing'!A130quot;),4,FALSE)

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

Hope this helps.

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

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

Pete_UK wrote:
gt;
gt; I think what you want is:
gt;
gt; .=VLOOKUP(A3,INDIRECT(quot;'I:\Fld\[Filquot;amp;C1amp;quot;.xls]Pricing'!A130quot;),4,FALSE)
gt;
gt; Basically, the inner part of the formula is built up as
gt;
gt; quot;'I:\Fld\[Filquot; amp; C1 amp; quot;.xls]Pricing'!A130quot;
gt;
gt; i.e. you are joining three strings together - the first and last are
gt; literal strings so must be enclosed in quotes, whereas the middle
gt; string is what is contained in cell C1. The INDIRECT( ) function allows
gt; you to combine addresses in this way.
gt;
gt; Hope this helps.
gt;
gt; Pete

--

Dave Peterson

Good points, Dave. I forgot!

Petethanks for all the help lads.....even though Dave had to go and quot;p**s
on my firequot; 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
JasonYou 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.

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

--

Dave Peterson


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(quot;'I:\Fld\[Filquot;amp;C1amp;quot;.xls]Pricing'!A130quot;),4,FALSE)
Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range(quot;C2quot;)) Is Nothing Or Target.Cells.Count gt; 1 Then Exit Sub
ActiveSheet.EnableCalculation = True
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

With Application.FileSearch
.NewSearch
.LookIn = quot;C:\quot; ' Change the file path to your suite
.Filename = [A2] amp; quot;.XLSquot;

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

End If
End With

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

End Sub
----------------------
vane0326
------------------------------------------------------------------------
vane0326's Profile: www.excelforum.com/member.php...oamp;userid=14731
View this thread: www.excelforum.com/showthread...hreadid=521125