Forum Discussion

dthomp74's avatar
dthomp74
Copper Contributor
May 28, 2020

vba code assist to hide/show based on drop-down list selections

I need assistance with building a dashboard that will default to hide all rows based on "select role" and "select location"  and will only produce results based when both drop-downs have changed. Can...
excel
Macros and VBA
hynguyen's avatar
hynguyen
Iron Contributor
May 29, 2020

dthomp74 Please see my suggestion in the attached sample workbook. I do not use VBA but use array formula based on some assumptions:

1. The results to be shown are Contact names (if you want to have more columns to show, you can copy the formula in column J over and modify "Contact" named range to whichever named range you want to show)

2. Currently I only put the array formula in column J of Tab Dashboard from J6:J27, based on max possible results of Tab Data for each combination of Role and Location.

3. L4 is dropdown for Role and J4 is dropdown for Location (use Data Validation/List instead of Inserting controls with VBA)

4. The array formula in J6:J27 would be:

{=IFS(OR(ISBLANK($J$4),ISBLANK($L$4),AND($J$4="Select Role",$L$4="Select Location")),"",TRUE,IFERROR(INDEX(Contact,SMALL(IF($J$4&$L$4=RoleLocation,ROW(RoleLocation)-MIN(ROW(RoleLocation))+1,""),ROW(A1))),""))}

 

Resources