Concatenate inside a formula

[gberg]

I have the following formula that I would like to uses a cell reference as part of the formula.

=SUMPRODUCT(--(JobList_01_Jan[Type]=Year_Current),--(JobList_01_Jan[Status]=Status_Open),JobList_01_Jan[JPR Rev])

I have the "JobList" table for each month so there is JobList_01_Jan, JobList_02_Feb, etc.

I wanted to use a cell reference for the month so I could change the month and get the results for that month

I tried this but got a zero value where there should be a value
=SUMPRODUCT(--("JobList_"&AA107&"[Type]"=Year_Current),--("JobList_"&AA107&"[Status]"=Status_Open),"JobList_"&AA107&"[JPR Rev]")

Thanks for your help

 

[ExceLoki]

try this

=SUMPRODUCT(--((INDIRECT("'JobList_"&AA107&"'!Type")=Year_Current),--(INDIRECT("'JobList_"&AA107&"'!Status")=Status_Open),INDIRECT("'JobList_"&AA107&"'!JPR Rev")))

 

[gberg]

That worked! Thanks for your help

Greg

 

[ExceLoki]

np, happy to help