Case Problems

Applied Technology Training Inc.

Jack Griffey is creating a workbook to assist in scheduling students for workshops. He wants to be able to share this information with both his assistant Claire as well as with his technically minded pal. He has gotten a start on the worksheet and has saved it as EX10XTRA04. Now, he wishes to finish his work. Assume the role of Jack and perform the steps that he identifies.

1. Jack downloads the current list of classes and saves it as EX10XTRA05. Next, he opens the EX10XTRA04 Excel file and save it as "Scheduler" to his personal storage location. He reviews the contents of the scheduler worksheet. He wants to use this worksheet to look up information in the workshops worksheet. Ideally, Claire would simply have to enter the name of the application into the yellow box at the top and the sheet would tell her the date and time of the class, as well as whether or not there were empty slots. If slots are available, she would be able to enter the information in the yellow boxes at the bottom. (He still hasn?t quite figured out where it is going from there, but for now he figures he can have her e-mail the changes to him.)

2. He then turns his attention to the empty workshops worksheet. Instead of retyping the information from the web page, he chooses to import it. Although he is familiar with web queries, he feels that it would be far simpler to copy and paste the table over. He opens the EX10XTRA05.htm file in his web browser, selects the table and copies it. He returns to Excel, selects cell A1 and pastes the table in. He sorts the table by Workshop. His workshops worksheet resembles the one in figure 10.5.

   Figure 10.5 Imported Table

   <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=gif:: ::/sites/dl/free/0072470941/26807/ECh10_acq_Image14.gif','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (10.0K)</a>

   He saves the file and turns his attention to the next exercise.

3. Jack wants to create the lookups he envisioned. First, to make things easier, he names the data range on the workshops worksheet. He selects the call range A2 through F15 then types workshops in the name box.

4. Next, he clicks on scheduler worksheet tab to switch to it. With the table in place, it has become clear that creating the look up formulas will be a snap. He selects cell B5 and types =vlookup( and then clicks the Paste Function (<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=gif:: ::/sites/dl/free/0072470941/26807/ECh10_acq_Image15.gif','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (0.0K)</a> ) button. Using the dialog box, he enters cell B3 as the lookup value, workshops as the table array, and column 4 for the column index. The formula doesn?t work. His tech-minded pal Dave Beauvais informs him that it is because there is no exact match. So, he advises Jack to change the formula in cell B5 as follows:

   =VLOOKUP($B$2&" "&B4,workshops,4)

   This change appends the text in cell B4 to the text in cell B2 for the purpose of the lookup. Jack then copies this formula from cell B5 to cells C5 and D5.

   Using variations on this formula, Jack completes the table so that it appears similar to figure 10.6.

   Figure 10.6 Completed table

   <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=gif:: ::/sites/dl/free/0072470941/26807/ECh10_acq_Image16.gif','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (9.0K)</a>

   As a final step, he applies document protection to everything but the yellow cells.

5. Jack wants suggestions on how to improve the scheduler. He decides to e-mail it to his friend Dave. Dave suggests that Jack send him the XML file for the workbook. Jack exports the file as XML to his personal storage location. He names the XML file "Scheduler XML". He e-mails the XML file to Dave (or his instructor). Jack takes a coffee break.

Data File: ex10xtra04 (13.0K)
Data File: ex10xtra05 (1.0K)