Excel__N00B
New Member
- Joined
- Jul 21, 2015
- Messages
- 5
I am hoping to create a dashboard for use in undertaking spot checks of work undertaken by members of my department.
Data will be entered into a spreadsheet in the format of:
<tbody>
</tbody>
I would like for there to be a dashboard where the user can select a date range. A chart will then be generated which has the percentage of green, amber and red ratings for the months within the date range.
This is where I am struggling to find a solution. The data is simple to record in the spreadsheet. However, I would like the user to be able to select a date range on the dashboard which then calculates the percentages for each month, e.g. Jan 2015 = 100% Green, 0% Amber and 0% Red. This is then used to populate a chart.
A static solution will not work as data will be continuously added. I have been struggling with terminology so my searching of Google has not turned up a great deal. I am thinking pivot tables may be of some use but thought I would check here before wasting hours.
Thanks.
Data will be entered into a spreadsheet in the format of:
| ID | Date | Status |
| 1 | Jan 2015 | Green |
| 2 | Jan 2015 | Green |
| 3 | Jan 2015 | Green |
| 4 | Feb 2015 | Amber |
| 5 | Feb 2015 | Red |
| 6 | Mar 2015 | Amber |
<tbody>
</tbody>
I would like for there to be a dashboard where the user can select a date range. A chart will then be generated which has the percentage of green, amber and red ratings for the months within the date range.
This is where I am struggling to find a solution. The data is simple to record in the spreadsheet. However, I would like the user to be able to select a date range on the dashboard which then calculates the percentages for each month, e.g. Jan 2015 = 100% Green, 0% Amber and 0% Red. This is then used to populate a chart.
A static solution will not work as data will be continuously added. I have been struggling with terminology so my searching of Google has not turned up a great deal. I am thinking pivot tables may be of some use but thought I would check here before wasting hours.
Thanks.
