• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple choice checkboxes X3

S

ScottJB

New Member
Hi All


First time poster, long time lurker (great site btw Chandoo). Intermediate at best Excel user, so i'm looking to those more proficient than i am from this excellent site.


Background : Still on Excel 2003 for now. Majority of my dynamic reports typically combine CHOOSE (linked to a Combo-Box (Forms)) + Named ranges + VLOOKUPs to give the end user selectable options for a dynamic chart/data.


I'm trying to create a new report which i thought may be easy in theory, but in practice i seem to be going around the houses. I'm trying to create a dashboard where the end user makes checkbox choices on 3 data fields (Division, Region, Location), i want them to be able to make multiple choices in each checkbox section. For arguments sake (the data i'm showing is fictional for business confidentiality) there are around 20 locations, 30 regions, 20 divisions.


Data looks similiar to below;

[pre]
Code: 
Division	Region	Location	Measure	Vol
Sales Division	NE	London	Sales Volume	14
Sales Division	SE	Birmingham	Sales Volume	22
Upgrade Division	NE	Birmingham	Sales Volume	7
Upgrade Division	NE	London	Sales Volume	3
[/pre]

I've created the check boxes (via Forms) in the display sheet. These are linked to a control sheet behind with a simple IF formula to convert the TRUE's into 1. Then combining COUNTIF,ROWS, & INDEX & MATCH formulas i've got as far as having 3 dynamic ranges of the selections made in the 3 checkboxes


Division Selections //Region Selections// Location Selections

*Multiple choices below each column header*


The dynamic Division Selections have a named range called "Division", the dynamic region selections have a named range called "Region", and the dynamic location selection have a named range called "Location".


I'm now trying to sum the sales volumes based on the choices made. SUMPRODUCT seems to fit the bill.


On the sheet where the original data is located, I've tried the following formula to do this:


=SUMPRODUCT(--(A1:A1000=Division),--(B1:B1000=Region),--(C1:C1000=Location),--(D1:D1004="Sales Volume"))*E1:E1000


This is returning a #NA result.


From a results perspective (using the fictional data above). If the user selected:


Checkboxes Option 1 : Sales Division

Checkboxes Option 2 : NE & SE

Checkboxes Option 3 : London & Birmingham


I'd want the result to return 36.


Can anybody help? Am i on the right track even?!


Unfortunately due to internet restrictions i'm unable to upload a dummy workbook so i've tried to include all the relevant information. Hopefully not too long winded!


Thanks in advance
 
Additional to the above that may help explain my issue


From searching the site, Chandoo did a post regarding formulas to lookup values based on multiple conditions below...


http://chandoo.org/wp/2010/11/02/multi-condition-lookup/


I guess ultimately what i'm asking for in the long winded first post, is how would i alter the formulas if we were trying to total the net sales for (dynamic salesman - could be more than 1) (dynamic region - could be more than 1) (dynamic product - could be more than 1). The dynamic ranges all be controlled by the checkbox selections in the display page.


I downloaded and altered Chandoo's workbook as an experiment, changing the "valSalesman" named range to include Joseph & Maria and this caused all the alternative lookup formulas in his workbook to return an error value (except the INDEX & MATCH option which didn't change).


Therein lies my problem. Can't seem to get the formula to work when the criteria for the SUMPRODUCT is both variable & potentially multiple (depending on how many checkboxes the user selects for each field)


Thanks again
 
Good day ScottJB


Perhaps the work book in my dropbox will help


https://dl.dropbox.com/u/34893656/Multi_Vlookup-1.xlsx
 
Thanks bobhc. I couldn't figure out how this would help me specifically unfortunately. As mentioned - i think i'm a bit lost with it now and can't see the wood for the trees.


I've managed to remove the business data from my workbook, simplify, and upload to dropbox. Hopefully this helps to show what i'm trying to achieve.


https://www.dropbox.com/s/aeu9uns2n9v1uti/New%20Report_Fictional.xls


Hopefully someone can assist.


Regards
 
ScottJB


I tackled it differently and changed your named formula


dynSubUnit: =OFFSET(Control!$A$135,,,SUMPRODUCT(--(Control!$A$135:$A$169<>"")),1)

dynSection: =OFFSET(dynSubUnit,,1)

dynLocation: =OFFSET(dynSubUnit,,2)


That fixed your formula in Data!G2


Have a read of: http://chandoo.org/wp/2011/11/18/formula-forensics-003/
 
Thanks Hui - great of you to have taken a look at my post.


I've just changed the "Refers to" criteria for the 3 named ranges to the OFFSET formulas you've pasted above.


However, i still get the #NA error in Data!G2? Have i mis-applied your post?


Thanks


Scott
 
Try retyping the " quotes manually as sometimes they get screwed up by wordpress

or

download here: https://www.dropbox.com/s/zbx3f8ehasea7o5/New%20Report_Fictional-1a.xls
 
Thanks again Hui


Now working....


BUT it doesnt calculate the correct total if i wanted to make multiple checkbox selections e.g. two locations, multiple business units etc. Seems to work when i limit it to one selection from each section (Sub Unit, Section, Location) fine.


For example selecting Sub Unit2>Section F>Loc 10 & 12 via the Display checkboxes. I would expect Data!G2 to return 109. At the moment it returns 105 (the total for Loc 10 only).


Is this even possible?
 
Hi Scott ,


Can you first clarify the following :


Are the checkboxes selections to be ANDed together or ORed together ?


Obviously , it is Loc 10 OR Loc 12 ; but is it :


Sub Unit 2 AND Section F AND ( Loc 10 OR Loc 12 )


or is it :


Sub Unit 2 OR Section F OR ( Loc 10 OR Loc 12 )


Narayan
 
Hi NARAYAN991


Thanks for looking. Its the former. Sub Unit 2 AND Section F AND ( Loc 10 OR Loc 12 )


Taking it further, if the end user made the following checkbox selections:


CHECKBOX FIRST AREA : Sub Unit 1, Sub Unit 2

CHECKBOX SECOND AREA : Section E, F, G

CHECKBOX THIRD AREA : Location 1,2,3


I'd want the total number for Sub Unit 1 OR Sub Unit 2, where the Section was E OR F OR G, AND where the location was 1 or 2 or 3.


(Sub Unit 1 OR Sub Unit 2) AND (Section E OR Section F Or Section G) AND ( Loc 1 OR Loc 2 OR Loc 3)


I'd want the end user to be able to make multiple sub unit, section, and region selections.


The logic appears to become more complex the more i think about it. If you are able to look into it further it would be greatly appreciated.


Regards


Scott
 
Hi Scott ,


Can you try this array formula , entered using CTRL SHIFT ENTER ?


=SUM(--(((IF(IF(ISNA(MATCH(A2:A57,Control!A135:A169,0)),0)>0,ROW(A2:A57)*0+1))*(IF(IF(ISNA(MATCH(B2:B57,Control!B135:B169,0)),0)>0,ROW(B2:B57)*0+1))*(IF(IF(ISNA(MATCH(C2:C57,Control!C135:C169,0)),0)>0,ROW(C2:C57)*0+1)))>0)*(D2:D57))


Narayan
 
Narayan


WOW. Thank you so much.


Appears to be working correctly based on limited tests. I'll develop the report further today and drop back into this thread later in the week with progress. I'll also try to understand exactly how your formula above is working.


Regards


Scott
 
Thanks again Narayan. Your formula has allowed me to progress nicely today.


Due to the number of checkboxes i'd like to have 3 master checkboxes i.e. a Location master checkbox that will allow me to select all/de-select all locations, a Section master checkbox that will allow me to select all/de-select all sections etc.


From some fairly limited research i think i'm likely to require VBA to do this. Something i have literally zero experience in. Tell a lie, i have created a command button to print reports in one go but thats my current limit!


A lot of the code i've found after googling (a) seems to select all checkboxes on the sheet (i'd only want the location master checkbox to select/deselect all location checkboxes, not all checkboxes on the sheet) and (b) seems to be code designed for checkboxes implemented using Control Toolbox rather than Forms.


Can anyone advise here?


Thanks in advance


Scott
 
Hi Scott ,


Can you check this file ?


https://www.dropbox.com/s/jyfxvzr8qsd5tpk/New%20Report_Fictional.xls


I have done it for one section viz. the Business Sub Unit. I am sure you can do the same for the other two sections.


Narayan
 
Narayan you are an absolute legend.


Worked perfectly, and it was easy for me to adapt the code to work for the other two sections.


Thanks again


Scott
 
You must log in or register to reply here.
Back
Top