Hello everyone,

I'm referencing an external workbook and need part of the workbook name to be a variable with a SUMPRODUCT formula. The initial formula always need me to manually change part of formula each time, very frustrating.
My base formula is; =SUMPRODUCT(--('[_Budget 2014.xlsx]Jan'!$K:$K=$A$244);'[_Budget 2014.xlsx]Jan'!$L:$L)
I need "2014" to be a variable, with the new information pulled from a cell
within the referencing workbook.

What is the syntax for this reference? I've try with CONCATENATE to build-up the reference. Numerous combinations and have not had any luck:
- =SUMPRODUCT(--(concatenate(Setup!A1;P247;Setup!A2);concatenate(Setup!A1;P247;Setup!A3)))
- =SUMPRODUCT(--(concatenate(“&'[_Budget&”;P247;Setup!A2);concatenate(“&'[_Budget&”;P247;Setup!A3)))
- =SUMPRODUCT(CONCATENATE(Setup!A1;P247;Setup!A2);CONCATENATE(Setup!A1;P247;Setup!A3))

Variable are:
Setup!A1 = '[_Budget
P247 = 2015
Setup!A2 = .xlsx]Jan'!$K:$K=A247
Setup!A3 = .xlsx]Jan'!$L:$L

The ' seems to be part of the problem with the Concatenate formula. Don’t know how to solve it.
Does someone know what the problem is?

Thanks in advance.