Vlookup won't work, any other ideas?
 

Hello all, I have a problem and I hope you can help me solve it.

I am creating an excel spread sheet to collect data from other sheets and
compare them. I want this sheet to be as automated as possible so that
anyone can use it.

There is a new source sheet created every week. My idea was to have a cell
on the main sheet to input the file name of the new sheet above every column,
and then have something like vlookup use that cell as a reference to collect
the data.

This won't work as vlookup won't resolve the link to the reference cell, so
all I get is #N/A.

Does anyone have any other ideas.

TIS Huw Thomas