Hello
I have a scorecard where there is a data tab and the scorecard tab. For the data I have period (month) column A, days in period (B), Beds (C), numerator (D), denominator (E), calculated value (rate of C/D) and target (G). The numerator, denominator and value all have dynamic named ranges (Period, Occ_N, Occ_D, Occ_val).
On the scorecard worksheet I have the months showing from E to P formatted to MMM. I successfully have the value per month showing using a formula i.e. for April in column E = Index(Occ_val,match(E3,period,false)).
So what I would like is to dynamically create the year to date totals for the 6 months of data in the current file. How do I determine there are 6 months of valid data on the scorecard and then extract from the named range numerator and denominator the same 6 periods of data to sum and come up with YTD. I would like to avoid creating another worksheet to calculate year end/ytd.
One other note is that the data will be many years i.e. this template just needs to have the user change the E to P rows of months to a new year to use so I can't just rely on referring to APR because there could be more than one APR.
Thank you for any assistance you can provide.
I have a scorecard where there is a data tab and the scorecard tab. For the data I have period (month) column A, days in period (B), Beds (C), numerator (D), denominator (E), calculated value (rate of C/D) and target (G). The numerator, denominator and value all have dynamic named ranges (Period, Occ_N, Occ_D, Occ_val).
On the scorecard worksheet I have the months showing from E to P formatted to MMM. I successfully have the value per month showing using a formula i.e. for April in column E = Index(Occ_val,match(E3,period,false)).
So what I would like is to dynamically create the year to date totals for the 6 months of data in the current file. How do I determine there are 6 months of valid data on the scorecard and then extract from the named range numerator and denominator the same 6 periods of data to sum and come up with YTD. I would like to avoid creating another worksheet to calculate year end/ytd.
One other note is that the data will be many years i.e. this template just needs to have the user change the E to P rows of months to a new year to use so I can't just rely on referring to APR because there could be more than one APR.
Thank you for any assistance you can provide.
