So I have a spreadsheet where the values in column B are essentially filenames. I want a function that will look up whatever is in column B and use that for a directory path to find a cell in that workbook.
I don't want to use indirect cause I need to be able to update the sheet I am working on without having all workbooks open.
I might not be able to explain this well, and I have no idea how to do this so I will try again.
I have values in column B. In column C i would write for example vlookup(C1$, C:\...\[B$4.xls]Sheet1'!$G$37:$H$39, 2, false).
This obviously doesn't work, so I tried using the concatenate function, but you cant do that either...
I am sure you can do this in macro, but I have no idea how to use VBA. If anyone could help that would be great!

Thanks

A