Embedded Files
Linking Sheets within a Spreadsheet
Linking Sheets within a Spreadsheet
Google Sheets can dynamically link data from one Sheet to data in another Sheet so that any changes you make in one Sheet are automatically reflected in the other Sheet.
You will need two Sheets or Tabs within a single Spreadsheet to create a link. The first Sheet will serve as the source of your data. The second Sheet will be dependent on the data in the first Sheet. Both Sheets will be contained in a single Google Spreadsheet.
Do the following:
➤ Open your "Company" spreadsheet.
➤ Create a new Sheet by clicking the "Add Sheet" button (+) located towards the bottom of the screen. A new blank sheet will appear.
➤ Rename the Sheet as "budget". To rename the Sheet, click the arrow next to the tab called "Sheet2" and select Rename, then type "budget"
➤ Rename the Sheet1 as "company"
Do the following:
➤ Enter the following data into the "budget" sheet:
Cell A1: Marketing Budget
Cell A3: Available Income
Cell A6: Proposed Expenses
Cell A12: Remaining Funds Available
Cell B4: Total Net Income
Cell B7: Website
Cell B8: Book Club Events
Cell B9: Conference Travel
Cell B10: Total
Cell C7: 5000
Cell C8: 10000
Cell C9: 2500
➤ Select cell C10 and enter the formula =SUM(C7:C9) to calculate the Total Expenses
➤ Select cell C12 and enter the formula =C4-C10 to calculate the Remaining Funds Available
To achieve the correct Remaining Funds Available on the "budget" sheet, we must enter the company's net income in cell C4. The company's net income data will come from the previous sheet that you created and renamed as "company".
➤ In the "company" sheet, find the cell that contains the total annual Net Income. Note that it should be cell F18.
➤ In the "budget" sheet, select cell C4 and enter the formula
=company!E18
This formula calls the company sheet by using "company!" and calls the cell "E18" from the company sheet to get the data. This is an important concept to understand!
➤ Experiment with a What If? analysis by entering 75,000 into cell B12 of the company sheet to see what happens if the quarter 1 Wages increase.
What happens to the Total Net Income and Remaining Funds Available on the budget sheet when the change is made?
One of Spreadsheet's major features is its ability to produce charts that illustrate the numbers on the sheets that you produce. In the next part of the tutorial you will learn how to create and format a Pie Chart.
Page updated
Google Sites
Report abuse