Lonnie wants to combine the profit and loss data for 2022 from each of the three Lewellen offices. In cell E6, enter a formula using the SUM function, 3-D references, and grouped
pls help with these qns
Lonnie prefers that accountants and others contact Lewellen through the link in cell H8 rather than one that connects to his email address. In cell C21, remove the hyperlink, leaving the unlinked text "Lonnie Holtzman" in the cell.
Lonnie wants to combine the profit and loss data for 2022 from each of the three Lewellen offices. In cell E6, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell C6 in the Charlotte:Milwaukee worksheets. Copy the formula from cell E6 to cell E7, the range E9:E11, the range E13:E15, and the range E17:E19. In the range E17:E19, copy the formula and number formatting only.
Go to the Charlotte worksheet. Lonnie needs to calculate the percent of sales for each expense item. Assign the defined name Revenue to cell C6.
In cell D7, enter a formula without using a function that divides the Cost of sales value in cell C7 by the revenue amount, using the defined name Revenue for the Charlotte worksheet. Copy the formula from cell D7 to the range D9:D11, the range D13:D15, and the range D17:D19. In the range D17:D19, copy the formula and number formatting only.
Lonnie wants to apply consistent formatting to the worksheets he collected from the three Lewellen offices. Group the Charlotte, Denver, and Milwaukee worksheets, and then apply the Heading 1 cell style to cell B2 to use the same formatting as in the Dashboard worksheet. Ungroup the worksheets, and then verify that the formatting is applied to all three worksheets.
Right click on the cell C21 and click Remove Hyperlink.
Click cell E6. Enter this formula: =SUM(Charlotte:Milwaukee!C9) where Charlotte:Milwaukee is 3D reference and !C6 helped to group worksheets. It brought the value of C6 from both worksheets. Copy the formula to E7, E9:E11, E13:E15. Right copy and paste. BUT, for E17:E19 right click and select "Paste Formula" option becasue that will replace the formatting of that range.
Right click of cell C6. Select Define Name and enter Revenue if it not entered as default.
Click on cell D7 and enter C7/Revenue. Copy and past the formula. In range D17:D19 make sure u just paste the formula not the stylings. Right click and it will give paste options.
Hold CTRL or CMD and click Charlotte, Denver, and Milwaukee worksheets. After that click B2 and then click cell styles of home tab. Choose Heading 1 style.
=SUM(Charlotte:Milwaukee!C9)where Charlotte:Milwaukee is 3D reference and !C6 helped to group worksheets. It brought the value of C6 from both worksheets. Copy the formula to E7, E9:E11, E13:E15. Right copy and paste. BUT, for E17:E19 right click and select "Paste Formula" option becasue that will replace the formatting of that range.C7/Revenue. Copy and past the formula. In range D17:D19 make sure u just paste the formula not the stylings. Right click and it will give paste options.Video Instructions: