All,
I need your help to modify the VBA code that is currently assigned to all (3) Filters (Combo Box Form Controls) in the DB Tab. Specifically, I need each filter in the DB tab to filter and maintain each filter level.
Then, when I select “All” on each filter, it should clear each filter level in the DATA tab, just as if you were filtering directly in the DATA tabs. Currently, the VBA code filters only one at time and does not hold multiple filters simultaneously.
All I’m trying to do is filter from the DB tab instead of the DATA tab to create a cleaner Dashboard rather than having all the DATA and calculations on the same tab.
Normally, I would use a pivot table for my dashboards from all the amazing examples your site has, but unfortunately the pivot table does not allow COUNTIFs Array Formulas in its calculated fields to count filtered visible cells data. Sure I can have the array formula calculate from the pivot table data source, but the data source will not be filtered. So I have to use a different option.
Additionally, I need some help with the COUNTIFS ARRAY formulas that I have in this spreadsheet that are based on multiple criteria. I am trying to figures out how to make them adjust according to visible filtered items from the DATA tab. I have the subtotal functions working properly on all my other calculations, but I'm having issues with the COUNTIFS type ARRAY formula adjusting and calculating the filtered data when the data is filtered or not.
I’m almost there I just need these final component to accomplish my objective. This will be great because it will another great alternative when I don’t have a pivot table option.
I have attached the XL file with more clarification and questions regarding this initiative.
I appreciate your help so much guys!
I need your help to modify the VBA code that is currently assigned to all (3) Filters (Combo Box Form Controls) in the DB Tab. Specifically, I need each filter in the DB tab to filter and maintain each filter level.
Then, when I select “All” on each filter, it should clear each filter level in the DATA tab, just as if you were filtering directly in the DATA tabs. Currently, the VBA code filters only one at time and does not hold multiple filters simultaneously.
All I’m trying to do is filter from the DB tab instead of the DATA tab to create a cleaner Dashboard rather than having all the DATA and calculations on the same tab.
Normally, I would use a pivot table for my dashboards from all the amazing examples your site has, but unfortunately the pivot table does not allow COUNTIFs Array Formulas in its calculated fields to count filtered visible cells data. Sure I can have the array formula calculate from the pivot table data source, but the data source will not be filtered. So I have to use a different option.
Additionally, I need some help with the COUNTIFS ARRAY formulas that I have in this spreadsheet that are based on multiple criteria. I am trying to figures out how to make them adjust according to visible filtered items from the DATA tab. I have the subtotal functions working properly on all my other calculations, but I'm having issues with the COUNTIFS type ARRAY formula adjusting and calculating the filtered data when the data is filtered or not.
I’m almost there I just need these final component to accomplish my objective. This will be great because it will another great alternative when I don’t have a pivot table option.
I have attached the XL file with more clarification and questions regarding this initiative.
I appreciate your help so much guys!