VBA to change file path based on cell value

I am attempting to save a lot of my time by trying to pull data from hundreds of different workbooks all within the same directory; the name of each workbook is in included in its own column in my lookup sheet. I already have enough data consolidated to where I can use an INDEX-MATCH to pull the required data, but the problem I'm running into is having to change the source file name within the formula every time the source file changes. Could a code be created that changes the source file name based on a cell value? Current formula listed first; file path and other references obviously changed. The source file name is located in the A column. The second formula is my desired result, although I know the formula will not work as such.

=INDEX('C:\Folder1\Products\[Product1.xlsx]Data'!$M$4,MATCH($L$1,''C:\Folder1\Products\[Product1.xlsx]Data'!$M$3,0))
=INDEX('C:\Folder1\Products\[$A3.xlsx]Data'!$M$4,MATCH($L$1,''C:\Folder1\Products\[$A3.xlsx]Data'!$M$3,0))

Hi there,

This is very difficult to test without access to your workbook and your directory structure, but you could try:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Originally Posted by Greg M

Hi there,

This is very difficult to test without access to your workbook and your directory structure, but you could try:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Greg,
I apologize for the late reply. Unfortunately, this does not work either. I removed the spaces and "&" but still nothing. The most it would do is before I removed anything; it would bring up a file path in a new window for me the choose the correct path. I attempted to create a VBA that copies the file name to the formula for every cell, but I had no success creating that either.

Please Login or Register  to view this content.

Anyone care to take a shot at this?