I'm trying to write a code that will dynamically pull the name of a closed workbook from a cell, and then reference a value from the closed workbook. I chose to use formula INDEX but it does not seem to correctly incorporate a referenced workbook name. I get a #REF! Invalid Cell Reference Error. Here's an example of what I tried:
A B 1 GDI-007 =INDEX('[GDI-007.xls]Test Data'!$AJ$7,1,1) 2 GDI-008 =INDEX(CONCATENATE("'["&A2&"]Test Data'!$AJ$7"),1,1)
Cell B1 correctly gives me the referenced value. However, Cell B2 throws the #REF! error. Any help or suggestions would be greatly appreciated.
Thank you in advance!
Bookmarks