Excel Expert Requested

Excel Expert Requested

CASE PROBLEM 3

Case Problem 3

Data File needed for this Case Problem: Cookin.xlsx

Cookin’ Good Cookin’ Good sells specialized home cooking products. The company

employs individuals to organize “Cookin’ Good Parties” at which the company’s products

are sold. Cleo Benard is responsible for entering sales data from various Cookin’ Good

Parties. She wants to design an Excel workbook to act as a data entry form. She has

already created the workbook, but she needs your help with setting up data validation

rules, creating a table lookup, and writing the macros to enter the data. Complete the

following:

1 . Open the Cookin’ workbook  located in the Excel8\Case3 folder included with your

Data Files, and then save the workbook as Cookin’ Good. In the Documentation

sheet, enter your name and the date.

  1. Go to the Sales Form worksheet. Create appropriate defined names for each cell in

the range C3:C8. Assign the name ProductInfo to the range E4:G1 5.

  1. In the Sales Form worksheet, create the following validation rules:
  2. The criteria allows only one of five regions (represented by the numbers 1 , 2,

3, 4, and 5) to be entered in cell C3. Enter an appropriate input message and

error alert.

  1. The criteria provides the list of 1 2 products (found in range E4:E1 5) in cell C4.

Enter an appropriate input message and error alert.

  1. The criteria allows only whole numbers greater than 0 to be entered as the number of units sold in cell C7. Enter an appropriate input message and error alert.
  2. In cells C5 and C6, enter a VLOOKUP function that automatically enters the product name and price into the sales form when the ProductID is entered. (Hint: Cells

should be blank if an error value appears in a cell.)

  1. In cell C8, enter a formula that automatically calculates the total sale for the order,

which is determined by the number of units sold multiplied by the price of the product. Use the IFERROR function to display a blank cell if an error value occurs during

the calculation of Total Sale.

  1. Prevent users from entering data into any cell in the Sales Form worksheet other than

cells C3, C4, and C7, and then protect the Sales Form worksheet.

  1. Test the data entry form by entering the following new record: Region = 1, Product

ID = CW, Units Sold = 8.

  1. Save the workbook, and then create a macro named AddData with the shortcut key

Ctrl+d that performs the following tasks:

  1. In the Sales Form worksheet, copy the values in the range C3:C8. (Hint: You’ll

paste later in the macro.)

  1. Switch from the Sales Form worksheet to the Sales Record worksheet. Click

cell A1 .

  1. Turn on Relative References.
  2. Use the End+ keys to locate the last used row in the first column. Then, press

the  key to move to the next row in the first column.

  1. Turn off Relative References.
  2. Paste the copied values from Step A into the blank row. (Hint: Use the Paste button arrow, and then click Paste Transpose button.)
  3. Switch to the Sales Table worksheet.
  4. Click inside the PivotTable and refresh the contents of the PivotTable to include

the new data.

  1. Switch to the Sales Form worksheet and clear the values in cells C3, C4, and C7.
  2. Make C3 the active cell.
  3. Stop recording the macro.
  4. Create a button in the range C1 1 :C1 2 on the Sales Form worksheet and assign the

AddData macro to the button. Change the button label to Transfer Sales Data.

1 0. Test the data entry form and AddData macro by entering the following new records:

Region Product ID Units Sold

3 HR 4

4 OEG  2

1 1 . Create a macro named ViewTable with the Ctrl+t shortcut key that displays the con-

tents of the Sales Table worksheet.

1 2. Create a macro named ViewChart with the Ctrl+c shortcut key that displays the

Sales Chart worksheet.

1 3. Create a macro named ViewForm with the Ctrl+f shortcut key that displays the Sales

Form worksheet. Test each macro using its shortcut keys.

1 4. In the Documentation worksheet, create three macro buttons below row 1 3 to view

the Sales Table worksheet (Step 1 1 ), the Sales Chart worksheet (Step 1 2), and the

Sales Form worksheet (Step 1 3). Assign the appropriate macro to each button, and

change the labels on the buttons to be more descriptive.

1 5. The Sales Table worksheet displays the total product sales in each region. Change the

PivotTable to show the values in the cells as percentages of the Column Total. (Hint:

On the Options tab, in the Calculations group, click the Show Values As button,

and then click % of Column Total. You can return the original value by clicking No

calculation.)

1 6. Create two macros. The first displays the PivotTable as percentages of column

totals (Step 1 5). Name this macro ShowAsPercent. The second macro displays

the PivotTable as numbers (values shown as No Calculation). Name this macro

ShowAsNumber.

1 7.  Edit the ShowAsPercent macro to display the results with a percent style by adding

the following three commands immediately before the End Sub statement:

Range( “ C5: H17 ” ) . Select

Selection. Style = “ Percent”

Range( “ A1” ) . Select

1 8. Create two macro buttons below the PivotTable in the Sales Table worksheet to run

each macro. Assign a macro to each button, and then change the button labels to be

more descriptive.

1 9. Save the workbook as CG with Macros, and then close it

Get a 10 % discount on an order above $ 100
Use the following coupon code :
BEST16
error: Content is protected !!