Using a list of cell values as filenames to link cells...

[broadarmel]

Here's my scenario... I have about 80 workbooks that contain project data each workbook is named something like "ABC 101-11.xls", "ABC 102-11.xls", "ABC 103-11.xls" and so on. These filenames are listed in range A7:A80 in my master project workbook. Cell D8 of each workbook contains the current total project costs and I'd like to link cell D8 of each individual project workbook to the range D7:D80 in my master workbook so I can see a list of projects with their associated costs. I know I can use the indirect function if I have the individual workbooks open and just copy and paste values so I can close the individuals but my issue lies in having to manually point to each workbook when I copy my indirect formula from D7-D8-D9 and so on. Vlookup doesn't work since my individual project workbooks are a template that is printed out for our PMs and not arranged in a manner that would allow a vlookup. Is there any formula or VBA macro I can do this without having to manually point to each individual workbook? It kind of defeats the purpose of using the indirect function because I've noticed if I change the filename value in column A, it doesn't change my lookup anyway.

 

[drsarao]

Have you tried this?
=INDIRECT("[" & A7 & "]Sheet1!D8")

You will have to remove the ".xls" from file name though.
And assuming that D8 always resides in "Sheet1"

 

[broadarmel]

I've tried the INDIRECT function but it forces me to manually select each workbook, which I would need to do over 80 times. I created a workaround that has made it a lot easier but still not the best way to go about it. However, it cut the time involved in preparing this report by about 75% so it's a win for now.

Thanks.