By using cell linksShare information from multiple sheets in real timeIt will also be easier to do. Extract specific values from one or more source sheets andRead-only summary sheetCan be imported into.In this case, if the information in the source sheet is updated, the data in the summary sheet will be updated automatically.
If you use this function,Department budget summary,Partner Sales Forecast,Personnel planning,Business Plan,Client project summaryYou can save the trouble of work in various situations such as.No need to copy and paste anymore.
Follow the step-by-step instructions to create a cell link.
Scenes using cell links
When pulling information from multiple sheets and displaying it
This is the case when creating rollup sheets for multiple projects.
It's functionally similar to a report, but because it's linked to a sheet, you can add your own columns, use functions, automate, conditional formatting, and more.
Also, as detailed below, there is no need to share the original sheet.
When sharing the information of the original sheet (s) without giving the sharing authority of the original sheet
It can be used to share information about a particular cell you want to share from the original sheet, which is inconvenient to be viewed in its entirety, to clients, co-workers, etc. involved in the project.
For example, if there is information that is difficult to see, such as information on other clients or cooperating businesses, budgets, evaluations of related parties, etc., it is possible to show only the necessary information excluding them. increase.
For example, in the sheet above, the columns in orange are inappropriate information to share with vendors and customers, such as vendor ratings.
This sheet, which links information with cell links, links only the necessary information from the original sheet, allows vendors to update the supply chain status with update requests, and allows customers to share that information. It is a situation.
If you do not want a sharer with edit permissions to edit a particular cell
For example, you can use it if you don't want the shared person to edit the value, such as the budget amount of the project or the evaluation value of another person in the performance evaluation.
Compared to row and column locks, it is more flexible on a cell-by-cell basis.That is, you can make only some cells in a column uneditable.
If multiple projects are linked, you can set a date dependency between the projects by directly or indirectly linking the end date of the previous project to the start date of the next project.
When centrally managing information used in other sheets such as constants and budget
This allows you to keep the values up to date across multiple sheets.
For example, if you are using the tax rate in a function, you can put a link from the original sheet, and if the tax rate changes, you can change the value of the original sheet to put the link on all the sheets. The tax rate will be updated.
Cell Link: Consolidate or maintain data integrity
Cell links are useful for integrating information from multiple sheets.With cell link,Create a rollup sheetOrTrack date dependencies between projectsOrKeep values up to date across multiple sheets
You can link cellsonlyis.You cannot link entire sheets, columns, or rows.Only cells that contain or previously contain data can be linked to the destination sheet.In the cellhyperlinkとCell linkCannot contain both.
Link type overview: inbound and outbound
There are two types of cell links:
- Of the cellInbound linkGets the value from a cell on another sheet.
The cell containing the inbound link is that link"Linked cell"And the sheet containing the linked cell is"Linked sheet"is.The target cell can have only one inbound link.The linked cell is indicated by the light blue arrow to the right of the cell.
- The cellOutbound linkIf contains, the value in that cell updates the cell in another sheet.
The cell containing the outbound link is that link"Original cell"And the sheet containing the original cell is"Original sheet"is.The original cell can be linked to multiple target cells.The original cell is indicated by the gray arrow at the bottom right of the cell.
To display the sheet name for an inbound or outbound link, select the linked cell.
To access a sheet with linked values, select the linked cell, hover over the text that appears, and click the link to the sheet.
To delete an inbound or outbound link, hover over the information that appears and click the Delete link.
Create an inbound cell link
To create a cell link, you must have at least the viewer permission of the original sheet and the editor permission of the linked sheet.
- Open the linked sheet.
- Click the cell and on the toolbar [Cell link] Click to display the cell link window.
- On the left side of the windowSheet selectionIn the section for, select the sheet that contains the original cell.
Tip: If you need to search a sheet, use the search bar.
- On the right side of the window, select the cell you want to reference [Create Link] Click.
A link is created from the referenced cell and a blue arrow appears to the right of the linked cell.
Tips for effective use of cell links
Creating an inbound link automatically creates an outbound link on the original sheet.
You can select multiple cells and create a link in each cell:
- The linked cells of the linked sheet are displayed in the same order as the original sheet.
- This action overwrites any data currently contained in the linked cell.
Links from the same original sheet to 500 cells at a timeCan be created,Up to 20,000 inbound links on the linked sheetYou can set the.
To prevent endless approval loopsFormulas between sheetsor sending us a message onCell linkCells that contain do not trigger automations that automatically modify the sheet (move rows, copy rows, lock rows, unlock rows, request approval).To avoid thisTime-based automationor sending us a message onPeriodicConsider using a workflow.
Edit or delete the link
Sheet owners and collaborators with editor or administrator access can edit or delete cell links.
Inbound link
You can edit the inbound link by double-clicking the inbound link and selecting the new original cell in the cell link form.
To remove an inbound link from a cell or group of cells:
- Click (or click and drag to highlight the group of cells) that contains the inbound link.
- Right click [Delete Link] Choose.
Alternatively, you can delete the links one at a time by selecting each cell and clicking the delete link that appears (on the LinkedIn to message to see the delete link). You need to hover over it).Alternatively, click the cell (or group of cells) that has the inbound link and then press the Backspace key to remove the link.
Outbound link
Outbound links should be deleted one by one.To remove outbound links:
- Select the original cell from the sheet that contains the outbound link.
- Hover your cursor over a linked cell to see the delete link.
- Of the link [Delete] Click.
Note: Deleting a sheet row that contains linked cells will affect the cell link.Deleting a row that contains the original cell breaks the cell link in the destination sheet.Deleting a row that contains a linked cell removes the link from the original sheet.
Create a link using the conditional paste feature (starting from the original sheet)
When starting from the linked sheet, ま た はCreate links to the same original cell in multiple linked sheetsIf you want toConditional pastePlease use.
To create a link using the conditional paste feature:
- Open the original sheet and copy the cell or range of cells(Right-click or use keyboard shortcuts).
- Open the destination sheet, click the cell where you want to create the link, then right-click (Ctrl + click for Mac users). [Conditional paste] Select and[Conditional paste] Display a window.
- [Link to copied cell] Select an option,[OK] Click.A link will be created from the selected cell to the copied cell.
note: You cannot create a link in a blank row or in a cell that you deleted after copying.
- Repeat steps 2 and 3 as needed to create other links in the cells you copied to another sheet.
Notes: Conditional paste seems to work fine when I move from the original sheet to the sheet to put the link in the same tab of the browser. (Confirmed by Cloudsmart. As of 2022.04.27)
Types of cells for which links are not available
You cannot create cell links in attachments / links or discussion columns.
When project / Gantt sheet dependencies are valid, You cannot create an inbound link for the next cell type on that sheet.
- Cell containing a column formula
- End date
- Predecessor task
- Parent rollup (start date, end date, duration, completion rate)
- Dependent start date
However, you can create links in the period and start date columns (if there are no predecessors in that row).The end date is calculated automatically and you will be able to add predecessor tasks after creating the link.
You cannot edit cells with inbound links in the following cases:
- From published sheet
- From the update request
- From the Smartsheet mobile app
- From the Smartsheet tablet app
- From the report
- From the edit window