Dynamic workbook reference, using Indirect on difference sheet, refering another file~

Create named ranges : File_Name, Sheet_Name and Column_Name in "Helper"



in "Summary"

in B2

=SUMPRODUCT(SUMIFS(INDIRECT("'[" & File_Name &"]" & Sheet_Name & "'!" & Column_Name &":" & Column_Name),INDIRECT("'[" & File_Name &"]" & Sheet_Name & "'!A:A"),$A2))

Copy down


NotE: ALL workbooks must be open.