How to make my excel report auto update
Attached is my current excel report with dashboard. Currently I transfer the numbers manually to tabs in blue to create graphs. I wish to reduce the manual data entry in the graphs' data.
I tried pivot chart, but pivot chart unable to calculate the rate and median for me. I'm using rate and median to plot my graphs.
I want to make the graphs' data can be auto updated when I input the Raw Data and Patient Days SOC tabs.
Please let me know if you can help me. Many thanks.
My suggestion is to convert the data on the Patient Days SOC from horizontal to vertical records as modeled in rows 10:15.
The columns for Hospital, date and days are manually input as was already being done in rows 1:2.
The column for falls is populated using: =COUNTIFS(Table1[Incident Location],"<>",Table1[Patient Type],"Inpatient",Table1[Incident Date],">="&B10,Table1[Incident Date],"<="&EOMONTH(B10,0))
The column for rate is populated using: =D10/C10*1000
The column for median is populated using: =MEDIAN([Rate])
Note that A10:F15 is an Excel table and that the formulas in columns D:F will update when data for a new month is added to the table.
I believe that the graph is self-explanatory, however let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks