Create an external link using another cell to form part of the file location

[GraYmOnD0808]

Hi Everyone,

This is one i have tried for ages at but have never managed to crack. It would be so useful to me if i could finally get it.

Essentially, for example based on the below image, i want to use column I to form part of the file name of the link i want to reference. So my formula for instance would read
='K:\Prod\Pick lists\ALDI\Aldi Salad (PTZ)\["I4".xlsm]ALDI Order'!$P$8

Is there a way to incorporate a cell into the link location? This way i can just drag my formulas down each day and have the cells populate without using a find and replace like i do now.


Thanks

Jimmy

 

[Fluff]

Hi & welcome to MrExcel.
How about

=INDIRECT("'K:\Prod\Pick lists\ALDI\Aldi Salad (PTZ)\["&I4&".xlsm]ALDI Order'!$P$8")

Although this will not work on closed workbooks.

 

[GraYmOnD0808]

Hi & welcome to MrExcel.
How about

=INDIRECT("'K:\Prod\Pick lists\ALDI\Aldi Salad (PTZ)\["&I4&".xlsm]ALDI Order'!$P$8")

Although this will not work on closed workbooks.

Ahh nice :D actually, i can make this work :D thanks!!

 

[Fluff]

You're welcome & thanks for the feedback.