Formula cell needed -> link to variable external link based on a cell content

Hi all,

I've tried to have a solution and did not found it yet. I need a formula to consolidate data from external references - and this is easy. But rows are related to different colleagues that have their own files with their name and the row order may change in their files.

What is the best formula to consider the external file name based on a cell text value from the consolidation workook?

For details please see attachment.

Thank you.
Sorin
excel.jpg

Hi Kaper,
I'm using multipple files from company sharepoint that are usually closed. INDIRECT function does not help.
Thank you.

Maybe this will work, but I'm not a VBA programmer. I can read some programming lines and adapt, but cannot create the code from scratch. Any hint?
If I'd have all code related to my example I could manage to modify to my needs.

Thank you.