Specifc Alphanumerics initates a find & replace function

Untested, but you could try this one.

c4 =Indirect("'C:\1KFC\Excel\2013\LY Sales\P1012\["&A1&" Numbers P1012.xls]Tracking'!I4")

INDIRECT doesn't work on closed workbooks. If this is a MUST, then you'll have to install more functionality into your Excel program. Only people with the same installed ADD-IN will be able to use your sheet.

The ADD-IN is called MoreFunc...read all about it here:
http://www.ashishmathur.com/tag/morefunc/

See step #4 of that page for instructions on downloading and installing

Go into TOOLS > ADDINS and activate MoreFunc.

Now you have many, many new functions available to you. Any place you used INDIRECT, now use INDIRECT.EXT instead and it will work on closed workbooks.

Now you can try oeldere's formula approach and it should work.

Hi,

Rather than Find & Replace you could use the Data Edit Links

i.e.

Please Login or Register  to view this content.

Hi,

Put the code in a Module level Procedure and then decide which event you want to trigger the change. This could be a change to either cell A1 or perhaps a button on the sheet which when clicked runs the macro. The procedure would be - changed from previously

Please Login or Register  to view this content.

Note. It assumes the workbook that has the links is in the same folder as the linked files and that there is only one link to change. If there are more than one then this will need changing.

With a button just right click the button and choose Assign Macro and pick the 'ChangeLinkedFile' procedure above.
If you want to trigger it when A1 is changed use the SheetChange event

Please Login or Register  to view this content.

Hi,

Your original post suggested that you were wanting to change all links throughout the workbook whereas I now understand that you want to selectively change links on specific sheets.
Sadly you can't do that with the Edit links functionality. It's all or nothing. And as Jerry has remarked INDIRECT() doesn't work on closed workbooks.

Without resorting to external add on tools or software it seems that the best way forward would be to change your formulae to incorporate INDIRECT functions and then get your macro to open up the workbooks that are linked to the sheets in question and immediately close them thus effectively changing the link.

Just a further point of clarification.

You said you have several different links.

If a link doesn't relate to more than one sheet then you can of course use Edit Links as I suggested - you'd have to work out the index number of the link and include that in the code I gave you. It's only if a link exists on two or more sheets and you only want to update one sheet that you couldn't use Edit Links.

And just a further idea. With the same link on two or more sheets, only one of which you want to change, you could I suppose temporarily change the links on the other worksheet by say temporarily adding some characters to the workbook name, or changing the formula to text by adding a text label marker with a Find/Replace, then do the Edit Links thus only affecting the one sheet, then reverse the changes you've just made.