Referencing closed workbook to get cell values

Question

I have searched the site but could not find a satisfactory answer.

I have the path of the directory in Cell A1 = C:\Sundeep\ I have the file name in Cell B1 = ibm

I want the value of cell C6 on sheet data in the file C:\Sundeep[ibm.xlsm]data!C6 and want to put it in cell C1 in the current workbook. I don't want to use vba code and I don't want to use indirect (as that requires the ibm.xlsm to be open).

If I put this string in C1 (hardcoded), then it works ='C:\sundeep[ibm.xlsm]data'!$C$6

However, if I try something like this

=CONCATENATE("'",A1,"[",B1,".xlsm]data'!")$c$6

it does not work.

Any help is appreciated.

Answer 1

You cannot use the cell value to be redirected to a workbook since even using Indirect() with the closed workbook will also fail.

You have to use Indirect.Ext() function of Morefunc add-in.