REF error with INDIRECT formula

[colinheslop1984]

Hi

Hoping someone can help, I need to gather data from multiple worksheets on multiple workbooks

The below gives me #REF error

=SUMPRODUCT(INDIRECT("'"&$C$5&"'[JAMIE SHEET METRO.xlsx]!'""'c$12:c$25"),$C$9:$C$22,INDIRECT("'"&$C$5&"'[JAMIE SHEET METRO.xlsx]!J$12:J$25"))

Cell C5 refers to tab names which can be changed using a drop down list
c12:c25 is criteria
j12:j25 is data needed

Is my formula structured wrong or is a different one needed?

Any feedback and possible suggestions would be much appreciated

 

[colinheslop1984]

That looks like its worked. Amazing thanks so much!

 

[colinheslop1984]

Is there a way to make it to work without all workbooks needing to be open?

 

[steve the fish]

Not with indirect no.

 

[Fluff]

@Colilheslop1984
Please supply links to the other site(s) where you have asked this question. As per post#9.
This is not optional.

 

[colinheslop1984]

What would be the alternative?

 

[RoryA]

Please read, and follow, posts 9 and 13.

 

[colinheslop1984]

https://www.excelforum.com/excel-fo...-sensitive-need-help-fixing-this-formula.html

 

[Fluff]

Thank you.

 

[colinheslop1984]

What would be the alternative to indirect?