Section 7.2. The Application

7.2. The Application

There are three display sheets, a Data sheet, and a Workarea sheet. The application uses the Data sheet to build the displays. If the Data sheet is updated all of the other sheets will update automatically. The display sheets contain no logic. They link to the information on Workarea using named ranges. The look and feel is based on patterns and borders.

The main display is named Totals and is shown in Figure 7-2.

Figure 7-2. The Totals sheet

This sheet has the look and feel of a web page. Our users regularly work with web pages. So, we make our application easy to understand by using the web page metaphor.

Item 1 is a general heading and appears on all of the display sheets. Item 2 is a simple bitmap created with Paint. It is drawn and copied in Paint, then pasted on the sheet using Paste Special Bitmap. It appears on all of the display screens, increasing their common appearance.

Item 3 is a list of the agents, showing their daily metrics. Average time per item is the average time it takes an agent to complete a work item in hours, minutes, seconds (hh:mm:ss). Item 4 gives the work group totals for the day. The headings in this area are also buttons that control the sort order. If the user clicks on the heading Items Worked, the area will sort by that item.

Item 5 contains navigation buttons. These buttons run simple macros that move the user between the display sheets. The sheet tabs do the same thing but there are a couple of reasons to include the buttons. First, there are sheets in the application that are not intended for display. The buttons keep users where they should be without actually hiding the data and Workarea sheets. Second, buttons are part of the web page metaphor. The user already knows what they do and how to use them.

Clicking the agent detail button causes the Detail sheet in Figure 7-3 to display.

Figure 7-3. The agent detail sheet

The agent detail sheet follows the look and feel of the application and focuses on a single agent. Item 1 displays the daily totals for the agent. Item 2 is a listbox that allows the user to select an agent to view. As soon as a name is clicked, all headings and displays change to that agent. There is no macro; everything is based on Excel functions and controls.

Item 3 is a chart that shows how many items the agent worked during each hour of the day. Each agent is expected to complete 150 items per day. The workday has seven hours, so therefore the expected work rate is about 21 items per hour. The red line in the chart shows the hourly standard of 21 items.

This is an example of extracting information from the data. All of the times are on the Data sheet, but with this chart the manager can see what is going on without looking at thousands of rows.

Clicking the agent timeline button takes the user to the Timeline sheet in Figure 7-4.

Figure 7-4. The Timeline sheet

The Timeline sheet shows each item the agent processed. Item 1 displays this information. The work time for each item is calculated along with the total time from one item to another.

The "Time to next" column shows the amount of time from the start of one item to the next. If this time is more than ten minutes, the row is flagged as in Item 2 using conditional formatting . With the "Time to next" area selected, we click Format Conditional Formatting. The dialog is filled out as shown in Figure 7-5.

We want to change the format to bold red if the time from one item to the next is more than 10 minutes. In Excel a date and time is really a number. The integer part tells the date and the decimal part is the time. We are working with minutes and there are 60 minutes in each of the 24 hours in a day. That is 1440 minutes. That means that there are 144 ten-minute periods in a day. So, ten minutes is 1/144 or 0.006944 of a day. That is why we use 0.006944 in the dialog.

Figure 7-5. Adding a conditional format