Making A formula change automatically

[Jamesallen116]

Hi,

Can someone please help if I have the formula,

='C:\Users\HL02\Desktop\Training Plan\Training Plan IX (F-Day)\[_Template IX Training Plan.xlsx]General'!$F$11

in cell D12 and I want (_Template) to automatically update to the name I cell A12,

If A12 was Joe Bloggs

='C:\Users\HL02\Desktop\Training Plan\Training Plan IX (F-Day)\[Joe Bloggs IX Training Plan.xlsx]General'!$F$11

Is there a way to modify the formula to do it on its own, or can it only be done with a find and replace?

 

[etaf]

you should be able to use indirect()
https://www.ablebits.com/office-addins-blog/2015/02/10/excel-indirect-function/
But will that file be closed ?
then
http://www.ashishmathur.com/tag/indirect-ext/
I have only used upto version 2013 of excel I think - maybe it was 2010 version
not certain on the later versions