User Form As Filter for Multiple Worksheets?

shellp

Board Regular
Joined
Jul 7, 2010
Messages
174
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello

I am not sure how to approach an issue related to a dashboard. I have one worksheet with all the data (RAW) and to start I have a worksheet called Volumes with various metrics. On Volumes there are 2 cells with data validation via drop down lists: hospital site (options ALL, Grey-Sloane, Mount_Sinai) in C2 and fiscal year (2017, 2018, 2019) in C1. As an example of total cases, the formula would be a sumif for when the selection is ALL or per site i.e.
VBA Code:
=If(S_Site="ALL",Sumifs(Raw!$CA$2:$CA1500,Raw!$B$2:$B$1500,"="&S_Fyear),Sumifs(Raw!$CA$2:$CA1500,Raw!$B$2:$B$1500,"="&S_Fyear,Raw!$A$2:$A$1500,"="&S_Site))
where S_Fyear is the selected fyear from Volumes C1 and S_Site is the selected site from Volumes C2.

But I want to add in another other filter like LOS group. Instead of creating an IF statement accounting for all scenarios of site and also LOS groups (i.e. 1 day, 2 to 3 days, 4 to 6 days etc), I am manipulating the raw data on the fly. On the volumes worksheet I have the various drop down list as above with fiscal and site and in C3 is a drop down with the examples like 1 day, 2 to 3 days, etc. and the resulting value is a named range of "LOS_grp". I create a column in the raw data worksheet (column CB): =if(LOS_grp="ALL","Y",If(BZ=LOS_grp,"Y","N") - where BZ is the value for the LOS group per record.
so the cells within the column changes when the user selects the drop down on the volumes worksheet. So I only have to include the code of this column = "Y" in the sumifs formula on the Volumes worksheet:
VBA Code:
=If(S_Site="ALL",Sumifs(Raw!$CA$2:$CA1500,Raw!$B$2:$B$1500,"="&S_Fyear,Raw!$CB$2:$CB$1500,"=Y"),Sumifs(Raw!$CA$2:$CA1500,Raw!$B$2:$B$1500,"="&S_Fyear,Raw!$A$2:$A$1500,"="&S_Site,Raw!$CB$2:$CB$1500,"=Y"))

So all this works great if I only have one worksheet of data so only one named range for LOS_grp to consider but I don't want the user to have to go back to the volumes worksheet to change the parameters for other worksheets. I also don't want multiple variations of LOS_grp named ranges per worksheet because the formula in the raw worksheet won't know which one to trigger depending on the worksheet I'm on.

So is there a way that I could use a user form that would filter for whatever worksheet I'm on? I am not new to Excel but I am new to user forms....is it possible to anchor it in the same position and be seen on each worksheet? How would the user form field be referenced in the sumifs formulae?

Any and all assistance greatly appreciated.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,144,281
Messages
5,723,471
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top