Data File needed for this Case Problem: Spirit.xlsx

The Spirit Store Alice Meachen established The Spirit Store, which sells products to loyal alums of Central State College. Products offered by Alice on her website range from tee shirts and backpacks to mugs and blankets—and all feature the school's logo. Alice has a large, steady base of clients who find these uniquely designed products a great reminder of their college days. To ensure the timely receipt of payments, Alice wants you to use Excel to create an invoice she can use for each customer transaction.

Complete the following:

1. Open the Sprit workbook located in the Excels? Case, folder included with your Data Ales, and then save the workbook as Spirit Store.

2. In the Documentation worksheet, enter your name and the date.

3. In the Product Pricing and Shipping worksheet, assign the defined name ShippingCost to the data stored in the range D2:E7, which can be used for an approximate match lookup. (Hint: The lookup table includes only the values, not the descriptive labels.)

4. In the Invoice worksheet, use data validation to make it easier to enter ordered items in the range C16:C35 by creating a list of the different items in the Product Pricing table in the Product Pricing and Shipping worksheet. (Hint: Select the entire range before selling the validation rule.)

5. In the Per Unit column (the range G10:035), use a VLOOKUP function to retrieve the per-unit price of each ordered product from the Product Pricing data in the range A3:B28 in the Product and Shipping worksheet. (Hint: Use the defined name ProductPrice that was assigned to the Product Pricing data.)

6. Modify the formula in the Per Unit column by combining the IFERROR function with the VLOOKUP function to display either the per-unit price or a blank cell H an error value occurs.

7. In the Total column the range H15:H36), enter a formula to calculate the total change for that row (Oty x Per Unit). Use the IFERROR function to display either the total charge or a blank cell H an error value occurs. Format the column appropriately.

8. In the Subtotal cell (cell H37), add a formula to sum the Total column. Use the IFERROF function to display either the subtotal or a blank cell H an error value occurs. Format this cell appropriately.

Data File needed for this Case Problem: Spirit.xlsx

The Spirit Store Alice Meachen established The Spirit Store, which sells products to loyal alums of Central State College. Products offered by Alice on her website range from tee shirts and backpacks to mugs and blankets—and all feature the school's logo. Alice has a large, steady base of clients who find these uniquely designed products a great reminder of their college days. To ensure the timely receipt of payments, Alice wants you to use Excel to create an invoice she can use for each customer transaction.

Complete the following:

1. Open the Sprit workbook located in the Excels? Case, folder included with your Data Ales, and then save the workbook as Spirit Store.

2. In the Documentation worksheet, enter your name and the date.

3. In the Product Pricing and Shipping worksheet, assign the defined name ShippingCost to the data stored in the range D2:E7, which can be used for an approximate match lookup. (Hint: The lookup table includes only the values, not the descriptive labels.)

4. In the Invoice worksheet, use data validation to make it easier to enter ordered items in the range C16:C35 by creating a list of the different items in the Product Pricing table in the Product Pricing and Shipping worksheet. (Hint: Select the entire range before selling the validation rule.)

5. In the Per Unit column (the range G10:035), use a VLOOKUP function to retrieve the per-unit price of each ordered product from the Product Pricing data in the range A3:B28 in the Product and Shipping worksheet. (Hint: Use the defined name ProductPrice that was assigned to the Product Pricing data.)

6. Modify the formula in the Per Unit column by combining the IFERROR function with the VLOOKUP function to display either the per-unit price or a blank cell H an error value occurs.

7. In the Total column the range H15:H36), enter a formula to calculate the total change for that row (Oty x Per Unit). Use the IFERROR function to display either the total charge or a blank cell H an error value occurs. Format the column appropriately.

8. In the Subtotal cell (cell H37), add a formula to sum the Total column. Use the IFERROF function to display either the subtotal or a blank cell H an error value occurs. Format this cell appropriately.

Data File needed for this Case Problem: Spirit.xlsx

The Spirit Store Alice Meachen established The Spirit Store, which sells products to loyal alums of Central State College. Products offered by Alice on her website range from tee shirts and backpacks to mugs and blankets—and all feature the school's logo. Alice has a large, steady base of clients who find these uniquely designed products a great reminder of their college days. To ensure the timely receipt of payments, Alice wants you to use Excel to create an invoice she can use for each customer transaction.

Complete the following:

1. Open the Sprit workbook located in the Excels? Case, folder included with your Data Ales, and then save the workbook as Spirit Store.

2. In the Documentation worksheet, enter your name and the date.

3. In the Product Pricing and Shipping worksheet, assign the defined name ShippingCost to the data stored in the range D2:E7, which can be used for an approximate match lookup. (Hint: The lookup table includes only the values, not the descriptive labels.)

4. In the Invoice worksheet, use data validation to make it easier to enter ordered items in the range C16:C35 by creating a list of the different items in the Product Pricing table in the Product Pricing and Shipping worksheet. (Hint: Select the entire range before selling the validation rule.)

5. In the Per Unit column (the range G10:035), use a VLOOKUP function to retrieve the per-unit price of each ordered product from the Product Pricing data in the range A3:B28 in the Product and Shipping worksheet. (Hint: Use the defined name ProductPrice that was assigned to the Product Pricing data.)

6. Modify the formula in the Per Unit column by combining the IFERROR function with the VLOOKUP function to display either the per-unit price or a blank cell H an error value occurs.

7. In the Total column the range H15:H36), enter a formula to calculate the total change for that row (Oty x Per Unit). Use the IFERROR function to display either the total charge or a blank cell H an error value occurs. Format the column appropriately.

8. In the Subtotal cell (cell H37), add a formula to sum the Total column. Use the IFERROF function to display either the subtotal or a blank cell H an error value occurs. Format this cell appropriately.

Data File needed for this Case Problem: Spirit.xlsx

Complete the following:

2. In the Documentation worksheet, enter your name and the date.