Using DATA VALIDATION drop down to set workbook name in SUMPRODUCT

[GSM1984]

Hi!

I'm hoping someone can help me please.

I have a formula in excel as follows:

<code>=SUMPRODUCT(('[XXXXX.xlsx]01 04 2014'!$E$2:$E$922=C$7)+0)
</code>

I also have a data validation drop down in cell B2 that lists multiple client names.
What I'd like todo is be able to use the drop down in B2 to allow me to change the workbook being referenced in the above formula.
Something such as:

<code>=SUMPRODUCT(('[$B$2.xlsx]01 04 2014'!$E$2:$E$922=C$7)+0)
</code>

^^ This obviously doesn't work, however I hope it gives an idea what I'm trying to achieve. Ideally I'd also like to use another cell to specify the <code>01 04 2014</code> reference.
Essentially I'm trying to create something like;

<code>=SUMPRODUCT(('[$B$2.xlsx]$C$3'!$E$2:$E$922=C$7)+0)
</code>

Can anyone help please?

 

[cyrilbrd]

Hi maybe something along =SUMPRODUCT((INDIRECT("'"&B$2&"'!$E$2:$E$922")=C$7)+0)

 

[cyrilbrd]

Or (misread your query)
given in B2 the workbook name
given in C3 the tab name

maybe something like =SUMPRODUCT((=INDIRECT("'[" & $B$2 & "]" & $C$3 & "'!$E$2:$E$922")=C$7)+0)

 

[GSM1984]

Or (misread your query)
given in B2 the workbook name
given in C3 the tab name

maybe something like =SUMPRODUCT((=INDIRECT("'[" & $B$2 & "]" & $C$3 & "'!$E$2:$E$922")=C$7)+0)

Thanks for your help, I apprecaite you taking the time to look at this.

I've tried your suggestion and sadly it produces and 'The formula you typed has contains and error' pop up

 

[GSM1984]

Ah I removed the = from the INDIRECT function and it now accepts the formula however produces a #REF! error

 

[GSM1984]

Nailed it - Was missing the .xlsx from the filename.

Thank you so much cyrilbrd.

 

[cyrilbrd]

Ah I removed the = from the INDIRECT function and it now accepts the formula however produces a #REF! error

Ha, good for spotting my carelessness.
Glad you got it sorted out, equal sign was indeed a typo.
Thanks for your feedback.

 

[cyrilbrd]

Nailed it - Was missing the .xlsx from the filename.

Thank you so much cyrilbrd.

Most welcome.