VLOOKUP linking to different files
 

I am using VLOOKUP in workbook to retrieve information from other files.
My formula looks like:
=VLOOKUP(B10,'C:\Documents and Settings\alopez\Desktop\Excel
Test\[Source1.xls]Sheet1'!$A$3:$B$7,2,FALSE)

A want to be able to retrieve information froma different type depending on
the value of cell. IE If the user enters a date in cell A1, I can form a file
name using a prefix and the date. Lets say the user enters 12/05/2007. I will
create the filename Source-12-05-2007.xls.

How do I pass automatically this value to the VLOOKUP function w/o having to
update the hardcoded value of the file name???

Regards

VLOOKUP linking to different files
 

Normally you would use the INDIRECT function to create a cell or range
reference from a text string, but it will not work with closed files.
Can you arrange to have the other file open at the same time? If the
file is open, then you do not need to have the full path in the
formula.

Hope this helps.

Pete

On Dec 6, 6:16 am, Alonso wrote:
I am using VLOOKUP in workbook to retrieve information from other files.
My formula looks like:
=VLOOKUP(B10,'C:\Documents and Settings\alopez\Desktop\Excel
Test\[Source1.xls]Sheet1'!$A$3:$B$7,2,FALSE)

A want to be able to retrieve information froma different type depending on
the value of cell. IE If the user enters a date in cell A1, I can form a file
name using a prefix and the date. Lets say the user enters 12/05/2007. I will
create the filename Source-12-05-2007.xls.

How do I pass automatically this value to the VLOOKUP function w/o having to
update the hardcoded value of the file name???

Regards

VLOOKUP linking to different files
 

Thanks Pete,
But the Source FIles are going to be open by different Destinations Files. I
am do not know how to program in VB and I am trying to avoid the learning
curve, at least for now.

Is therea way to query an Access DB from a excel spreadsheet?

I am thinking this will also require some programming, so may as well have
the Source files in Access Tables instead of isolated excel files.

Any ideas are appreciated.

Regards.

"Pete_UK" wrote:

Normally you would use the INDIRECT function to create a cell or range
reference from a text string, but it will not work with closed files.
Can you arrange to have the other file open at the same time? If the
file is open, then you do not need to have the full path in the
formula.

Hope this helps.

Pete

On Dec 6, 6:16 am, Alonso wrote:
I am using VLOOKUP in workbook to retrieve information from other files.
My formula looks like:
=VLOOKUP(B10,'C:\Documents and Settings\alopez\Desktop\Excel
Test\[Source1.xls]Sheet1'!$A$3:$B$7,2,FALSE)

A want to be able to retrieve information froma different type depending on
the value of cell. IE If the user enters a date in cell A1, I can form a file
name using a prefix and the date. Lets say the user enters 12/05/2007. I will
create the filename Source-12-05-2007.xls.

How do I pass automatically this value to the VLOOKUP function w/o having to
update the hardcoded value of the file name???

Regards