How to vlookup to a file name referenced in another cell

[beckyz38]

I am creating a dynamic dashboard in Excel that would allow users to pull information from another workbook based on the information entered.
For example:
Cell A1 enters where the workbook is saved: C:\Users\FilePath
Cell B1 enters the sheet name: suppose we want to look at sheet1
Cell C1 enters the range of the sheet - Let's say column A1 to C1
Cell D1 enters the column to look at - Suppose column B

The workbook that I am currently working on is saved in a different path. I tried to use the indirect formula however it does not work with closed workbooks. Is there a formula to do that without the referencing errors? Much appreciated!

 

[Fluff]

Hi & welcome to MrExcel.
The only way to do that with a formula is to use indirect, but as you have found it does not work with closed workbooks.