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.
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:
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.
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))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.
