Excel Dashboard and Automation Request for Testing Schedules
Budget: £20 – £40 GBP
Request Overview:
We need assistance in creating an Excel dashboard with live-updating graphs and trends for six testing schedules. The purpose of this dashboard is to automatically update whenever data is modified. Here's a breakdown of the requirements:
Key Requirements:
Schedules to be Tracked:
Environmental Schedule B1
Environmental Schedule B2
Environmental Schedule B3
Hand Swabs
Water Testing
Air Plates
Data Representation:
Raw Data for each schedule is highlighted in orange in our current file. do not alter the passes and fails as this is our actual up to date tests results, work so at the end it will include the current data, and we just continue with the schedule.
The Passes are indicated with the letter “P” in green cells.
Fails are highlighted in red, containing text and values representing the reason for failure. You can assign an “F” to represent fails, but we also need to know the type of tests it failed and the limits, not sure if can be incorporated as part of the same graph or needs a separate graph.
Scheduled Tests for analysis are in yellow cells, marked with an “S” (or another suitable method you recommend).
Visualizations:
I require live graphs for each schedule that show:
Total number of Passes and Fails.
Percentage of Passes and Fails.
Breakdown of Fails with specific failure reasons.
A separate graph for the Fails to display the particular reasons for each fail by week.
Visibility of scheduled tests (highlighted in yellow) for a particular week.
Dashboard Requirements:
A dashboard tab containing:
Visuals for each of the six schedules.
Weekly trends, including total number of scheduled tests, number of passes, and number of fails.
Yearly trend of total tests, passes, and fails (both in numbers and percentages).
Data should automatically update in the graphs when any data in the tables is modified.
Suggested Functions & Tools:
Utilize functions like Conditional Formatting, Formulas, Pivot Tables, Graphs, etc.
Feel free to format the data into a table for easier manipulation and dynamic updates
We welcome any additional tips, tricks, or improvements to make the format more user-friendly.
Please ensure the dashboard and the graphs are easy to use and manipulate for future updates.
Deliverables:
A functional Excel file with a dynamic dashboard and live-updating graphs.
Detailed instructions on how to maintain or modify the setup if needed.
Feel free to reach out if you have any questions or need clarification on any of the points.
We need assistance in creating an Excel dashboard with live-updating graphs and trends for six testing schedules. The purpose of this dashboard is to automatically update whenever data is modified. Here's a breakdown of the requirements:
Key Requirements:
Schedules to be Tracked:
Environmental Schedule B1
Environmental Schedule B2
Environmental Schedule B3
Hand Swabs
Water Testing
Air Plates
Data Representation:
Raw Data for each schedule is highlighted in orange in our current file. do not alter the passes and fails as this is our actual up to date tests results, work so at the end it will include the current data, and we just continue with the schedule.
The Passes are indicated with the letter “P” in green cells.
Fails are highlighted in red, containing text and values representing the reason for failure. You can assign an “F” to represent fails, but we also need to know the type of tests it failed and the limits, not sure if can be incorporated as part of the same graph or needs a separate graph.
Scheduled Tests for analysis are in yellow cells, marked with an “S” (or another suitable method you recommend).
Visualizations:
I require live graphs for each schedule that show:
Total number of Passes and Fails.
Percentage of Passes and Fails.
Breakdown of Fails with specific failure reasons.
A separate graph for the Fails to display the particular reasons for each fail by week.
Visibility of scheduled tests (highlighted in yellow) for a particular week.
Dashboard Requirements:
A dashboard tab containing:
Visuals for each of the six schedules.
Weekly trends, including total number of scheduled tests, number of passes, and number of fails.
Yearly trend of total tests, passes, and fails (both in numbers and percentages).
Data should automatically update in the graphs when any data in the tables is modified.
Suggested Functions & Tools:
Utilize functions like Conditional Formatting, Formulas, Pivot Tables, Graphs, etc.
Feel free to format the data into a table for easier manipulation and dynamic updates
We welcome any additional tips, tricks, or improvements to make the format more user-friendly.
Please ensure the dashboard and the graphs are easy to use and manipulate for future updates.
Deliverables:
A functional Excel file with a dynamic dashboard and live-updating graphs.
Detailed instructions on how to maintain or modify the setup if needed.
Feel free to reach out if you have any questions or need clarification on any of the points.