External File(s) referenced and file links change based on row cell.

I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.

I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.


Jimmy

You will need to use INDIRECT to do that, but unfortunately it only works if the other workbooks are open too...

.. to work with closed workbooks, you need to install a free addin called Morefunc.xll and use INDIRECT.EXT. Morefunc. xll

Something like:

=INDIRECT.EXT("'C:\DOCUMENTS AND SETTINGS\["&A2&".XLS]Sheet1'!$C$31")

copied down.

Note: The addin is embeddable so you can share file...

As this is a company computer and IT controls what is or is not added as an addin to any software program is there a work around or do I need to ask my IT folks if they can allow the addin?...then again..it would have be an an addin for others that would use this file in the future to add other external links to it.

Jim

That addin is embeddable as I said so that others don't have to re-install it on their workstations...

An alternative would be a UserDefinedFunction by Harlan Grove called PULL()

Read this article for more on working with external links and click the PULL hyperlink for the code. External Links

I think I am not understanding the use of the PULL command that well. I am getting a #NAME? error when using it so maybe I didn't insert it correctly in the VB? I am a novice at programming and have no knowledge of VB unfortunately.

My need is to have the file name increase incrementally when I copy the formula down in the rows, so I figured using some function within the larger function (where the filename goes) would do the trick..but it seems there is nothing like that?

Please Login or Register  to view this content.

I may be just not getting the Pull function I suppose or a work around to pull it all together, even if it is somewhat ineffecient..you have no idea how much our IT team is bull dogs over adding non authorized software...which is a good thing for network security really.

I never actually used the function (i just use the morefunc addin)..

.. but I think yuo just replace the indirect.ext function above with pull function

First hit Alt+F11 and go to Insert>>Module and paste the Pull code

Then in a cell on the sheet.

=PULL("'C:\DOCUMENTS AND SETTINGS\["&A2&".XLS]Sheet1'!$C$31")

or for your path

=Pull("'\\server\Desktop\Files\["&A2&".XLS]Sheet1'!$C$31")

copied down

Now when I copy down how can I get the A2 to increase as well? For some reason the A2 does not change or increase incrementally. Any ideas?

Thanks so much, you have been most helpful!

Jim

If you pasted the formula I gave, it works to change A2 to A3, etc....

if you changed anything, look closely at the syntax.. or post the actual formula here...

Just putting in my two cents.

I have a few files that have links to other files, based on the dates entered. I created a macro that does a find/replace for just the date. This goes through all the links and changes just the date part of the filename, leaving the filepath, extension and the rest of the filename as is.

Please Login or Register  to view this content.

One thing I just discovered and have yet to put in practice is ActiveWorkbook.Path. You could do the following, if you want the cells to be empty until a link is placed in them.

Please Login or Register  to view this content.

Thanks for the help guys. I decided to stick with NBVC's plan as I don't have dates to assign and it is not a "single" cell changing but rather a listing of values in column A that I want ot pull back corresponding values to in other spreadsheets. So imagine that you have a phone number assigned to 50 offices in your market and each office has its own spreadsheet. In the master spreadsheet you want to list all the offices on the left and pull in the phone number for each office. So you need a function that looks for the file with the name in column A (.xls) and pulls in a specific cell that is the same location in each file.

NBVC - so I tested with Indirect and it pulls in the values fine with the document open. I made sure to add Pull in the module as you said. I saved all documents, closed all documents, closed excel, opened up the master "totals" file again, replaced the Indirect with Pull and WALA! It worked.

Just letting you know. This was the code I used, with private info removed of course:

Please Login or Register  to view this content.

It pulled in from the closed workbook as if I had it open, but like the author said it is pretty slow. But it works, and I can run it and have coffee so I am good.

Thanks so much again for everyones help, this group has never let me down!