Interactive dashboard - selecting slicers

%3CLINGO-SUB%20id%3D%22lingo-sub-1586703%22%20slang%3D%22en-US%22%3EInteractive%20dashboard%20-%20selecting%20slicers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586703%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20%26nbsp%3BI%20am%20building%20an%20interactive%20dashboard%20in%20Excel.%20%26nbsp%3BI%20would%20like%20for%20users%20to%20be%20able%20to%20see%20information%20either%20by%20Neighbourhood%20or%20by%20Subregion.%20%26nbsp%3BIf%20they%20choose%20Neighbourhood%2C%20a%20list%20of%20neighbourhoods%2C%20based%20on%20the%20Neighbourhood%20slicer%2C%20will%20be%20available%20to%20them.%20%26nbsp%3BIf%20they%20choose%20Subregion%2C%20a%20list%20of%20subregions%2C%20based%20on%20the%20Subregion%20slicer%2C%20will%20be%20available%20to%20them.%20%26nbsp%3BBut%20I%20need%20these%20two%20slicers%20to%20be%20independent%20of%20one%20another%3B%20if%20one%20is%20selected%2C%20the%20other%20cannot%20be%20used.%20%26nbsp%3BCan%20I%20use%20a%20form%20control%20to%20have%20users%20indicate%20their%20selection%20-%20either%20Neighbourhood%20or%20Subregion%20-%20and%20then%20highlight%20the%20appropriate%20slicer%20and%20hide%20the%20other%20one%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1586703%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1586877%22%20slang%3D%22en-US%22%3ERe%3A%20Interactive%20dashboard%20-%20selecting%20slicers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F758432%22%20target%3D%22_blank%22%3E%40SSim047%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20SSim%2C%20Without%20more%20detain%20the%20most%20I%20can%20suggest%20is%20to%20have%20the%20information%20that%20is%20displayed%20be%20part%20of%20an%20%3DIF%20equation.%20In%20each%20cell%2C%20if%20the%20control%20selects%20%22neighborhood%22%2C%20(the%20cell%20will%20show%201%20or%202%2C%20in%20the%20case%20of%20a%20form%20control)%20then%20the%20neighborhood%20information%20displays%2C%20etc.%20If%20the%20information%20is%20placed%20on%20a%20hidden%20tab%20or%20cells%2C%20then%20you%20can%20update%20the%20information%20without%20having%20to%20change%20the%20formulas.%3C%2FP%3E%3CP%3EEx.%20If%20the%20form%20control%20is%20tied%20to%20A1%20(on%20sheet1%20or%20'sheet%201'%20if%20there%20is%20a%20space)%20and%20the%20neighborhood%20info%20is%20on%20sheet2%20starting%20with%20a1%2C%20and%20the%20subregion%20info%20is%20on%20sheet3%20starting%20with%20a1%2C%20then%20say%2C%20in%20a2%20of%20sheet%201%2C%26nbsp%3B%20%3Dif(a1%3D1%2Csheet2!a1%2Cif(a1%3D2%2Csheet3!a1%2C%22%22).%3C%2FP%3E%3CP%3EDepending%20on%20what%20is%20chosen%2C%20the%20reference%20will%20display.%20Do%20a%20formula%20for%20every%20cell%20on%20sheet%201%20that%20will%20have%20information.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1586978%22%20slang%3D%22en-US%22%3ERe%3A%20Interactive%20dashboard%20-%20selecting%20slicers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F758432%22%20target%3D%22_blank%22%3E%40SSim047%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20has%20to%20know%20somehow%20which%20names%20are%20for%26nbsp%3B%3CSPAN%3ENeighbourhood%20and%20which%20are%20for%20Subregions.%20If%20in%20your%20table%20one%20column%20with%20only%20one%20of%20these%20two%20values%20for%20each%20record%2C%20and%20another%20one%20with%20names%2C%20you%20may%20add%20two%20slicers.%20On%20first%20column%20and%20on%20second%20one%20accordingly.%20Thus%20selecting%26nbsp%3BNeighbourhood%20or%20Subregion%20by%20first%20slicer%20you%20have%20proper%20names%20in%20second%20one.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590584%22%20slang%3D%22en-US%22%3ERe%3A%20Interactive%20dashboard%20-%20selecting%20slicers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590584%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F628423%22%20target%3D%22_blank%22%3E%40SqueakySneakers%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20suggestions.%20%26nbsp%3BI%20solved%20this%20problem%20by%20creating%20a%20table%20with%20one%20column%20and%20two%20rows%3A%20Neighbourhood%20and%20Subregion.%20%26nbsp%3BI%20then%20created%20a%20Pivot%20table%20and%20%22Geography%22%20slicer%20based%20on%20this%20table.%20%26nbsp%3BI%20then%20wrote%20code%20to%20show%20or%20hide%20the%20slicers%20with%20the%20lists%20of%20Neighbourhoods%20or%20Subregions%2C%20depending%20on%20which%20was%20selected%20in%20the%20Geography%20slicer.%20%26nbsp%3BRather%20than%20using%20a%20form%20control%2C%20I%20linked%20the%20code%20to%20the%20cell%20that%20captured%20the%20selection%20in%20the%20Geography%20slicer%20and%20programmed%20it%20to%20run%20every%20time%20the%20cell%20changed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590585%22%20slang%3D%22en-US%22%3ERe%3A%20Interactive%20dashboard%20-%20selecting%20slicers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590585%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20suggestions.%20%26nbsp%3BI%20solved%20this%20problem%20by%20creating%20a%20table%20with%20one%20column%20and%20two%20rows%3A%20Neighbourhood%20and%20Subregion.%20%26nbsp%3BI%20then%20created%20a%20Pivot%20table%20and%20%22Geography%22%20slicer%20based%20on%20this%20table.%20%26nbsp%3BI%20then%20wrote%20code%20to%20show%20or%20hide%20the%20slicers%20with%20the%20lists%20of%20Neighbourhoods%20or%20Subregions%2C%20depending%20on%20which%20was%20selected%20in%20the%20Geography%20slicer.%20%26nbsp%3BRather%20than%20using%20a%20form%20control%2C%20I%20linked%20the%20code%20to%20the%20cell%20that%20captured%20the%20selection%20in%20the%20Geography%20slicer%20and%20programmed%20it%20to%20run%20every%20time%20the%20cell%20changed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello!  I am building an interactive dashboard in Excel.  I would like for users to be able to see information either by Neighbourhood or by Subregion.  If they choose Neighbourhood, a list of neighbourhoods, based on the Neighbourhood slicer, will be available to them.  If they choose Subregion, a list of subregions, based on the Subregion slicer, will be available to them.  But I need these two slicers to be independent of one another; if one is selected, the other cannot be used.  Can I use a form control to have users indicate their selection - either Neighbourhood or Subregion - and then highlight the appropriate slicer and hide the other one?

4 Replies

@SSim047 

Hi SSim, Without more detain the most I can suggest is to have the information that is displayed be part of an =IF equation. In each cell, if the control selects "neighborhood", (the cell will show 1 or 2, in the case of a form control) then the neighborhood information displays, etc. If the information is placed on a hidden tab or cells, then you can update the information without having to change the formulas.

Ex. If the form control is tied to A1 (on sheet1 or 'sheet 1' if there is a space) and the neighborhood info is on sheet2 starting with a1, and the subregion info is on sheet3 starting with a1, then say, in a2 of sheet 1,  =if(a1=1,sheet2!a1,if(a1=2,sheet3!a1,"").

Depending on what is chosen, the reference will display. Do a formula for every cell on sheet 1 that will have information. 

 

@SSim047 

Excel has to know somehow which names are for Neighbourhood and which are for Subregions. If in your table one column with only one of these two values for each record, and another one with names, you may add two slicers. On first column and on second one accordingly. Thus selecting Neighbourhood or Subregion by first slicer you have proper names in second one.

@SqueakySneakers Thank you for your suggestions.  I solved this problem by creating a table with one column and two rows: Neighbourhood and Subregion.  I then created a Pivot table and "Geography" slicer based on this table.  I then wrote code to show or hide the slicers with the lists of Neighbourhoods or Subregions, depending on which was selected in the Geography slicer.  Rather than using a form control, I linked the code to the cell that captured the selection in the Geography slicer and programmed it to run every time the cell changed.

@Sergei Baklan Thank you for your suggestions.  I solved this problem by creating a table with one column and two rows: Neighbourhood and Subregion.  I then created a Pivot table and "Geography" slicer based on this table.  I then wrote code to show or hide the slicers with the lists of Neighbourhoods or Subregions, depending on which was selected in the Geography slicer.  Rather than using a form control, I linked the code to the cell that captured the selection in the Geography slicer and programmed it to run every time the cell changed.