Jan 04 2021 08:24 AM
Jan 04 2021 09:20 AM
SolutionIn your case that could be like
=SUMPRODUCT(
(INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$B$2:$B$13")=$B3)*
(INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$C$1:$E$1")=C$1)*
(INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$C$2:$E$13"))
)
Please not, INDIRECT() doesn't work with closed file, thus full path is not required - you shall to keep Details opened.
Jan 04 2021 09:20 AM
SolutionIn your case that could be like
=SUMPRODUCT(
(INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$B$2:$B$13")=$B3)*
(INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$C$1:$E$1")=C$1)*
(INDIRECT("'[Details.xlsx]"&SUBSTITUTE(C$2,"'","''")&"'!$C$2:$E$13"))
)
Please not, INDIRECT() doesn't work with closed file, thus full path is not required - you shall to keep Details opened.