Hello,

I'm trying to get the value of a closed workbook.

I've done a lot of digging, and I know that INDIRECT function doesn't work with closed workbook.

Some were even suggesting using a third party add ins. However, due to company policy, I won't be able to install and download it. Even if I could, I won't be able to pass it on to my colleagues as they won't have the 3rd party add ins.

Some were saying to use INDEX formula. I did try it and it does work. Below is the trial formula which works.

=INDEX('\\NetworkFullPath\[WorkbookName MMM YYYY.xlsb]SheetName'!$A$1:$HZ$654,MATCH(D$6,'\\NetworkFullPath\[WorkbookName MMM YYYY.xlsb]SheetName'!$C$1:$C$654,0),MATCH($B14,'\\NetworkFullPath\[WorkbookName MMM YYYY.xlsb]SheetName'!$A$2:$HZ$2,0))
As you can see, this is static, if I want to make it dynamic, I'll have to change the pretty much from the NetworkFullPath to the SheetName.

I try to use CONCATENATE, but it seems that it'll always refer to as a string instead of a full path name, workbook name and Sheet Name. Thus, I thought of using INDIRECT to change the string to an actual value.

But no matter how I try, I always either get a #VALUE! or #REF! error.

What am I trying to do, is it feasible? In a sense having the dynamic path, workbook and sheet name, where these are located in the same workbook on a different sheet, and to finally able to evaluate and give the result of a closed workbook value.