Wild Planet Conservation

⦁ Andre Suarez is the senior director of Wild Planet Conservation, a nonprofit organization that supports African wildlife and operates in California, Oregon, and Washington state. He has asked for your help updating the worksheet he created to track and record financial information.
⦁ Switch to the Local Offices worksheet, and then unprotect it.
⦁ To make it easy to find detailed staff information stored in another workbook, create a hyperlink as follows:
⦁ In cell A7, insert a hyperlink to the Support_NP_EX16_CS5-8a_Staff.xlsx file, available for download from the SAM website. (Make sure all the files for this project are in the same folder.)
⦁ Use Staff Information as the text to display.
⦁ Use Detailed staff information for all locations as the ScreenTip text.
⦁ View the comment in cell A12. Follow the instructions in the comment to update a value in the worksheet, and then delete the comment.
⦁ Andre started to create named ranges in the worksheet and has asked you to finish the updates as follows:
⦁ Edit the defined name associated with cell D12 to be CA_Run instead of CA_RunWalk.
⦁ Create a defined name for cell D24 using WA_Run as the name.
⦁ Select the range F16:G20 and create names from the selection using the values shown in the Left column.
⦁ In cell G11, insert a formula using the SUM function that uses the defined names CA_Adopt, OR_Adopt, and WA_Adopt to calculate the total amount raised for the Adopt an Animal fundraiser.
⦁ To include a note about revenue from fundraisers, insert a comment into cell G13. Add This amount exceeded our goal this year! and if necessary, hide the comment so it is not a distraction.
⦁ To ensure the accuracy of data entered in the Event Coordinators table, create a data validation rule that accepts only values from a list as follows:
⦁ In cell G17, insert a data validation rule that accepts values from a List, ignores blanks, and appears as an in-cell dropdown.
⦁ Enter the list California, Oregon, Washington as the validation source.
⦁ For the input message, use Office Location as the title and Select an office location from the list. (including the period) as the Input message.
⦁ For the error alert, use the Stop style with Invalid location as the title.
⦁ Create a data validation rule that accepts only specified date values as follows:
⦁ In cell G18, insert a data validation rule that accepts Date values for dates between 3/1/2019 and 3/31/2019.
⦁ For the input message, use March Event Date as the title and use the text Enter date for March fundraiser. (including the period) as the Input message.
⦁ For the error alert, use the Stop style with Invalid March event date as the title.
⦁ Edit the data validation rule associated with cell G19 as follows:
⦁ Change the input message title to May Event Date and use Enter date for May fundraiser. (including the period) as the Input message.
⦁ Change the error alert to use the Stop style with Invalid May event date as the title.
⦁ Test the data validation rules by entering the following information into the worksheet:
⦁ In cell G16, enter Amanda Arnett as the Name.
⦁ In cell G17, select Oregon as the WPC Office.
⦁ In cell G18, enter 3/12/2019 as the March Date.
⦁ In cell G19, enter 5/15/2019 as the May Date.
⦁ In cell G20, select Run for the Wild as the Event.
⦁ Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
⦁ Group the California, Oregon, and Washington worksheets.
⦁ In cell A4, edit the text to read Patron (instead of Member).
⦁ Bold the values in the range G4:G8.
⦁ In cell B8, enter a formula using the SUM function that totals the revenue for January (the range B4:B7). Copy the formula to the range C8:F8.
⦁ Ungroup the worksheets and then check to confirm that all three worksheets reflect the changes you made in this step.
⦁ Switch to the Consolidated worksheet. Consolidate the revenue data from each of the offices as follows:
⦁ In cell A4, enter a formula without using a function that references cell A4 in the Washington worksheet. Copy the formula from cell A4 to the range A5:A7.
⦁ In cell B4, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell B4 in the California:Washington worksheets.
⦁ Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
⦁ Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.
⦁ Andre has set a revenue goal of $20,000 for January, February, and April, when the organization has no fundraisers, and a revenue goal of $200,000 for March and May, when the organization does have fundraisers.
⦁ In cell B10, enter a formula using the IF and AND functions to indicate whether the revenue goal has been met that month:
⦁ Enter the logical test using the AND function to determine if the Fundraisers amount in cell B7 equals 0 and the Total in cell B8 is greater than 20000.
⦁ If the logical test is true, display Yes (using “Yes” for the value_if_true argument).
⦁ If the logical test is false, insert a nested IF function.
⦁ Enter the logical test of the nested IF function using the AND function to determine if the Fundraisers amount in cell B7 is greater than 0 and the Total in cell B8 is greater than 200000.
⦁ If the logical test for the nested IF function is true, display Yes (using “Yes” for the value_if_true argument).
⦁ If the logical test is false, display No (using “No” for the value_if_false argument).
⦁ Copy the formula in cell B10 to the range C10:F10.
⦁ The conditional formatting rule Andre created in the range B10:F10 highlights months that do not meet the revenue goal. Remove the fill color from the highlighting as follows:
⦁ Edit the conditional formatting rule applied to the range B10:F10.
⦁ Change the format so the Fill color is No Color, while keeping the same font color and style.
⦁ Go to the Spring worksheet. In the Spring and Fall worksheets, Andre has stored membership revenue for the spring and fall of 2018. Format the Spring worksheet to match the Fall worksheet as follows:
⦁ Format the range A2:E56 as an Excel table with headers using the Lime, Table Style Medium 4 table style. (Hint: Depending on your version of Office, the table style may be written as Table Style Medium 4.)
⦁ Use SpringMemberships as the name of the table.
⦁ Andre wants to include a quick way to determine the level of the membership, which is based on the Amount values. Enter a formula using the HLOOKUP function as follows:
⦁ In cell C3, enter a formula using the HLOOKUP function.
⦁ Use a structured reference to the Amount column ([Amount]) as the lookup value.
⦁ Look up that value in the table array shown in the range G2:J3, using an absolute reference to that range.
⦁ Return the value in row 2 of the table array.
⦁ Use TRUE as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.)
⦁ Fill the formula into the range C4:C55, if necessary.
⦁ Clear the contents of cell C56 in the Total row.
⦁ Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows:
⦁ In the range A3:A55, apply a conditional formatting Highlight Cells Rule that formats any duplicate values with Light Red Fill with Dark Red Text.
⦁ Correct the duplicate values by updating the following Member ID values. (Hint: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)
⦁ Use CA-240 as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount.
⦁ Use WA-122 as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount.
⦁ Switch to the Fall worksheet. In the Fall worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the VLOOKUP function as follows:
⦁ Use cell H2 as the lookup value.
⦁ Use a reference to the FallMemberships table as the table array.
⦁ Use column 2 as the column index number.
⦁ Use FALSE as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2.