Using DATA VALIDATION drop down to set workbook name in SUMPRODUCT

GSM1984

New Member
Joined
Feb 17, 2015
Messages
4
Hi!

I'm hoping someone can help me please.

I have a formula in excel as follows:

Code:
<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:
<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:
<code>=SUMPRODUCT(('[$B$2.xlsx]$C$3'!$E$2:$E$922=C$7)+0)
</code>
Can anyone help please?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
365
Platform
Windows, Mobile
Hi maybe something along =SUMPRODUCT((INDIRECT("'"&B$2&"'!$E$2:$E$922")=C$7)+0)
 

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
365
Platform
Windows, Mobile
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

New Member
Joined
Feb 17, 2015
Messages
4
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

New Member
Joined
Feb 17, 2015
Messages
4
Ah I removed the = from the INDIRECT function and it now accepts the formula however produces a #REF! error
 

GSM1984

New Member
Joined
Feb 17, 2015
Messages
4
Nailed it - Was missing the .xlsx from the filename.

Thank you so much cyrilbrd.
 

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
365
Platform
Windows, Mobile
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,191
Messages
5,473,041
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top