Lonnie needs a quick way to refer to the budgeted payroll and operating expenses. Use name PayrollExp to define the range D9:D11. Use the name OpExp to define the range D13:D15.
Lonnie Holtzman is a financial analyst for the Lewellen Group, a management consulting firm headquartered in Providence, Rhode Island. The firm also has offices in Charlotte, North Carolina; Milwaukee, Wisconsin; and Denver, Colorado. Lonnie is using an Excel workbook with multiple worksheets to create a profit and loss statement for the firm. He asks for your help in completing the statement.
Go to the Dashboard worksheet. Lonnie first wants to complete the list of links in the range H7:H10. In cell H7, create a hyperlink to the www.lewellen.example.net website.
In cell H8, create a hyperlink to the info@lewellen.example.net email address. Use Contact a Lewellen consultant as the ScreenTip text.
In cell H9, create a hyperlink to cell A1 in the Categories worksheet.
In cell H10, create a hyperlink to the workbook Support_EX19_EOM5-1_2021.xlsx, which contains Lewellen profit and loss data from 2021.
Lonnie wants to compare 2021 profit and loss totals to those for 2022 and needs to add the 2021 data to the Dashboard worksheet. Open the file Support_EX19_EOM5-1_2021.xlsx. Copy the values in the range C6:C19. In cell C6 of the Dashboard worksheet in the original workbook, use the Paste Link command to create external references to the values in the Support_EX19_EOM5-1_2021.xlsx workbook. Delete the unnecessary values in cells C8, C12, and C16, and close the Support_EX19_EOM5-1_2021.xlsx workbook.
Lonnie needs a quick way to refer to the budgeted payroll and operating expenses. Use the name PayrollExp to define the range D9:D11. Use the name OpExp to define the range D13:D15.
In cell D18, enter a formula using the SUM function to total the expense values using the defined range names PayrollExp and OpExp.
Click H7. Right click and select link. Enter www.lewellen.example.net in the Address.
Click H8. Right click and select link. Select 'Email Address' tab. Enter info@lewellen.example.net in 'E-mail address:'. Click on ScreenTip... which is at top of right hand side and then enter Contact a Lewellen consultant.
Select H9 and, same as above, click link. This time, select 'Place in This Document' tab. Select Categories and A1 should be there as default.
For H10, repeat same step but select 'Existing File or Web Page' tab. Click at little folder icon at right-hand-side and locate the Support_EX19_EOM5-1_2021.xlsx file and click Ok or Open.
Open Support_EX19_EOM5-1_2021.xlsx file and copy C6:C19. Clikc on cell C6 of Dashboard worksheet. Right click and select Paste Link options from the Paste. Don't directly paste it, select Paste Link option. Delete 0s from C8, C12, and C16.
Select D9:D11 right and select Define and then enter PayrollExp. Repeat same for D13:D15 and enter OpExp.
=SUM(PayrollExp:OpExp)Video explanation: