Excel assignment, due 12/6

I’ve included some screenshots to show you what I’d like to see on your final project. The first is a screenshot of the Presentation Layer (“PL”) with all the drop-down boxes showing “(All)”.

Figure 1: First PL screenshot.

I’ve chosen to not show gridlines and to show Data Labels. This is NOT required. You may leave the gridlines in and exclude data labels if you wish. You do NOT have to use the same layout as I did, but it must be neat and professional-looking. If you need to make the columns a little wider to fit a box, feel free. Size the graph (and/or adjust the column widths) to fit nicely.

Now, notice the three-line title in Figure 2. The first line must include the date-range for the report. The title formula must pick up the date values from named cells in the analysis layer (“AL”). The second line will pick up the number of records that meet the criteria the user has chosen, also from a named cell, probably on the pivot table page, because that’s probably where the formula for the title is. It will also include the percentage of the total number of valid records. (If what you need for the first two lines doesn’t exist, create it. And name it.) If the user has selected any of the drop-down box choices, the third line will show them.

Figure 2.

If the user has chosen something from more than one of the boxes, the third line will show whatever he has selected.

Figure 3.

And if the user’s choices result in no records that meet all the criteria, the second title line is pointless so the 3rd line becomes the 2nd line as shown here in Figure 4.

Figure 4.

Now the good news – the third line is an “A” credit task. So is changing the shift values from 1, 2, 3 to 1st, 2nd, 3rd. The first two lines, as described, are required.

Avoiding a problem:

If the user chooses a month that’s not in the date range, the spreadsheet will explode. The VBA macro “Month” will be displayed with an almost meaningless error message, and it will show the offending macro code highlighted in yellow. There will be no instructions as to what the user should do, and nothing will work until the Reset button is clicked.

Here is an “A” credit task that will keep that from happening. First, replace the months that are outside of the data range with an appropriate phrase as shown in Figure 5.

Figure 5.

Second, if the user clicks on any of the “No data” months, the following minor addition to the “Month” macro will fix things. The user will see a message box pop up and politely tell him to choose a valid month (Figure 6).

Figure 6.

The little extra code is shown on the next page. The Times New Roman font is what you’ve already written. The Courier New font is the additional code you’ll need.

Here’s how to read the new code. In plain English, it says “If your choice was “(No data)” then here’s a message box that says to choose something else. Then I’m kicking you out of here. End of the IF statement.”

The message in the middle of the message box (Figure 6) is called the prompt. The title at the top is called – surprise – the title. The “OK” button comes automatically (but sometimes you might want other options, like maybe Yes and No, in case you’re asking a question). The button combinations have names like vbYesNo, vbOKOnly, and so on. The VBA equivalent of CHAR(10) is vbCrLf, which stands for “carriage return, line feed” which comes from old manual typewriters which you’re not old enough to remember. Finally, there is a “line continuation character” (the underscore) at the end of the first line.

The message box happens when you say this:

MsgBox “the text you want for the prompt – in quotes”, vbokonly, “the text you want for the title – also in quotes”

First comes the prompt, then the button(s), then the title. Courier New font shows exactly what you need to type. Type every space, everything, exactly as shown.

Public Sub Month()

'

Dim sFilterChoice As String

sFilterChoice = Range("MonthChoice")

'

If sFilterChoice = "(No data)" Then

MsgBox "There's no data for that selection... " & vbCrLf & vbCrLf & _

"Please choose a valid month.", vbOKOnly , "No data"

Exit Sub

End If

'

Sheets("PT_CT").PivotTables("PT1").PivotFields("Month").ClearAllFilters

Sheets("PT_CT").PivotTables("PT1").PivotFields("Month").CurrentPage = sFilterChoice

End Sub

Now for a prettier solution (but more typing). Leave the month names alone and change the “If” statement to

If sFilterChoice = “Jan” or sFilterChoice = “Feb” or sFilterChoice = “Sep” or… Then

and finish typing the rest of the months that are outside the date range of the report.

The next item is putting the user’s choice of Minimum Cleaning Time into a list box so that if he wants to change the value, he simply clicks on whatever value he wants and the graph adjusts, just like it does for different filters. Compare the numbers on this graph to those in Figure 1. Also notice that the X-axis label changes, too. The one-line code is shown just below.

Figure 7.

Public Sub ChangeMinCleanTime()

Sheets("PT_CT").PivotTables("PT1").PivotCache.Refresh

End Sub

And finally, the Error Report shown in Figures 1 – 4 is a simple listing of the values in the cells in AL that counted the errors. And, of course, everybody in college knows how to calculate a percentage.

Project Summary:

With pivot tables and graphs you can summarize and compare and contrast data that people with money care about. Everyone pays attention to graphs and summary numbers in a presentation, a speech, a campaign, a news broadcast, and even ordinary conversation. But none of that is worth anything unless the underlying data is accurate, reliable, and valid.

Reliability and validity will be covered in Stats, but the methods you will learn there simply assume that the data is accurate. What we’ve done this semester is spend some time, not enough, but some time, cleaning system-generated data.

If there’s one thing I’d like for you to take from this semester it’s this: Every data-based decision in every field of human endeavor requires good data. Identifying bad data is a mission-critical task in every industry.

Second thing: Excel is the tool that’s currently used for that purpose. It is the lingua franca of data preparation and analysis for department-level decisions.

Here’s some useful stuff for turning things in…

Other expectations:

1. Almost all cells and cell ranges are named with meaningful names in the MixedCase style.

2. The naming convention I mentioned for the PL filters (FilterList, Filter_CL, FilterChoice) is adhered to.

3. Using list boxes instead of drop-down, or combo, boxes is OK.

4. If you use list boxes for filters, you must use a drop-down box for minimum cleaning time, and vice versa.