blossomthe2nd
Active Member
- Joined
- Oct 11, 2010
- Messages
- 450
Hi Experts
I am about to embark on a project and I would like a little guidence if possible.
Due to hierarchy reasons in our team structure Im trying to design a dashboard that has many sumproduct formulas that caluclate based on variable conditions.
So, this is what I envision,
I open my spreadsheet and a VBA form pops up and looks a little like the below :
Checkboxes:
Ire
UK
US
NZ
OZ
ListBox
All the months
Submit button
Using the check boxes I tick as many countries as I want and using the drop down I select the month in which I'd like to calculate-
This data would then adjust my Sumproduct formula inline with selections -
So the Dropdown month would set a value (Jan = 1, Feb =2 etc)- column I
and the checkboxes would each create an array for column G
=SUMPRODUCT(--(MONTH('[Customers.xls]service'!$I$7:$I$31)=7),--('[Customers.xls]service'!$F$7:$F$31="Domestic"),--('[Customers.xls]service'!$G$7:$G$31="Ire"),--('[Customers.xls]service'!$G$7:$G$31="UK"),--('[Customers.xls]service'!$P$7:$P$31="External"))
If anyone can offer any assistance with any part of this I would greatly appreciate it
Thanks
A
I am about to embark on a project and I would like a little guidence if possible.
Due to hierarchy reasons in our team structure Im trying to design a dashboard that has many sumproduct formulas that caluclate based on variable conditions.
So, this is what I envision,
I open my spreadsheet and a VBA form pops up and looks a little like the below :
Checkboxes:
Ire
UK
US
NZ
OZ
ListBox
All the months
Submit button
Using the check boxes I tick as many countries as I want and using the drop down I select the month in which I'd like to calculate-
This data would then adjust my Sumproduct formula inline with selections -
So the Dropdown month would set a value (Jan = 1, Feb =2 etc)- column I
and the checkboxes would each create an array for column G
=SUMPRODUCT(--(MONTH('[Customers.xls]service'!$I$7:$I$31)=7),--('[Customers.xls]service'!$F$7:$F$31="Domestic"),--('[Customers.xls]service'!$G$7:$G$31="Ire"),--('[Customers.xls]service'!$G$7:$G$31="UK"),--('[Customers.xls]service'!$P$7:$P$31="External"))
If anyone can offer any assistance with any part of this I would greatly appreciate it
Thanks
A