Ch 6: Linking Spreadsheet Data
Why Link Data?
Working with Multiple Sheets
Add a sheet: click the + at the bottom, right-click a tab > Insert Sheet, or Sheet > Insert Sheet.
Referencing Other Sheets / Files
$'SheetName'.CellAddress. Example: $'Term 1'.C4 (single quotes are used because the name has a space).#$, then Sheet.Cell:'file:///C:/Users/ADMIN/Documents/X-A.ods'#$Result.C4///).Live data: Sheet > Insert Sheet > From File with the Link box ticked keeps the sheet always matching the original file.
Hyperlinks
| Type | Meaning |
|---|---|
| Absolute Hyperlink | Stores the complete file location. Breaks if the file is moved. e.g. C:\Users\ADMIN\Downloads\try.ods |
| Relative Hyperlink | Stores the location relative to the current location. Still works if the whole folder is moved together. |
Create: Insert > Hyperlink. Edit/remove by right-clicking the link.
Linking to External Data
Sheet > Link to External Data > enter the URL of an HTML web page > choose a table (or HTML_all for the whole page) — the table is inserted and stays linked.
Registered data source: register a Base database (.odb) via Tools > Options > LibreOffice Base > Databases so its data can be used in Calc.
Summary
- Linking references data so changes update automatically (no duplication errors).
- Reference format uses
$'Sheet'.Cell; file paths use three slashes (///). - Absolute hyperlink = full path (breaks if moved); relative = based on current location.
- External HTML tables and registered
.odbdatabases can be linked into Calc.
Quick check: 3 MCQs (tap an option)
Q1. Which reference points to cell C4 of a sheet named “Term 1” in the same file?
Calc uses $'SheetName'.Cell; quotes because of the space.
Q2. A hyperlink that still works when the whole folder is moved is:
It stores the location relative to the current file.
Q3. How many forward slashes come after “file:” in a file path reference?
Example: 'file:///C:/Users/…'.
Data Link क्यों करें?
कई Sheets के साथ काम
Sheet जोड़िए: नीचे + पर click, tab पर right-click > Insert Sheet, या Sheet > Insert Sheet।
दूसरी Sheets / Files Reference करना
$'SheetName'.CellAddress। उदाहरण: $'Term 1'.C4 (नाम में space होने से single quotes इस्तेमाल)।#$, फिर Sheet.Cell:'file:///C:/Users/ADMIN/Documents/X-A.ods'#$Result.C4///) होती हैं।Live data: Sheet > Insert Sheet > From File, Link box tick करने से sheet हमेशा original file से मेल खाती रहती है।
Hyperlinks
| Type | मतलब |
|---|---|
| Absolute Hyperlink | पूरी file location store करता है। File move होने पर टूट जाता है। जैसे C:\Users\ADMIN\Downloads\try.ods |
| Relative Hyperlink | मौजूदा location के सापेक्ष location store करता है। पूरा folder साथ move हो तो भी चलता है। |
बनाना: Insert > Hyperlink। Link पर right-click करके edit/remove।
External Data से Link
Sheet > Link to External Data > किसी HTML web page का URL डालिए > एक table चुनिए (या पूरे page को HTML_all) — table insert होकर linked रहती है।
Registered data source: Base database (.odb) को Tools > Options > LibreOffice Base > Databases से register कीजिए ताकि उसका data Calc में इस्तेमाल हो।
सारांश
- Linking data reference करता है ताकि बदलाव अपने आप update हों (कोई duplication error नहीं)।
- Reference format
$'Sheet'.Cell; file paths में तीन slashes (///)। - Absolute hyperlink = पूरा path (move पर टूटता); relative = मौजूदा location के आधार पर।
- External HTML tables और registered
.odbdatabases Calc में link हो सकते हैं।
झटपट जाँच: 3 MCQs (option पर tap करें)
Q1. उसी file की “Term 1” sheet की cell C4 का सही reference है:
Calc में $'SheetName'.Cell; space होने से quotes।
Q2. पूरा folder खिसकाने पर भी चलने वाला hyperlink है:
यह मौजूदा file के सापेक्ष location रखता है।
Q3. File path reference में “file:” के बाद कितने forward slashes आते हैं?
उदाहरण: 'file:///C:/Users/…'।
Frequently Asked Questions
Why is linking data better than copying it?
Linking references the data instead of copying it, so any change in the source is reflected automatically. This avoids duplication and prevents errors from out-of-date copies.
What is the reference format for a cell in another sheet of the same file?
The format is $'SheetName'.CellAddress, for example $'Term 1'.C4. Single quotes are used when the sheet name contains a space.
How many forward slashes does a file path use in a reference?
A file path uses three forward slashes, as in file:///C:/Users/ADMIN/Documents/X-A.ods.
What is the difference between an absolute and a relative hyperlink?
An absolute hyperlink stores the complete file location and breaks if the file is moved, while a relative hyperlink stores the location relative to the current one and keeps working if the whole folder is moved together.
