How to reference a cell on another sheet

Google Sheets: verified over 28 of 29 checks — 1 excluded as not comparable.

• ='Jon''s Data'!A1: the Drive importer renamed its data tab (“Jon's Data” → “Jons Data”), so the Sheets run isn’t comparable to the LibreOffice reference run.

Pull a value from another tab into a formula.

The formula

How it works

Any formula can reach another tab by prefixing the cell with the sheet name: Summary!B2 times Data!C5 gives 12 x 5 = 60. Excel and Google Sheets write SheetName!Cell; LibreOffice Calc's own A1 syntax writes SheetName.Cell with a dot, and that is what its formula bar shows you when you click a cell on another tab. The separator is a display and typing question, not a storage one — a workbook saved as .xlsx carries the exclamation-mark form, and LibreOffice reads and runs it as-is, so files travel fine and only hand-typed formulas need the right character for the app you are typing into. The rules worth knowing beyond that: sheet names containing a space or punctuation go in single quotes ('Q1 Data'!A1); every range in a formula needs its own prefix, because an unprefixed one silently means the current sheet; whole columns work across tabs (Data!B:B); INDIRECT can assemble the reference from a cell so the tab name becomes data; a 3-D reference (Q1:Q3!A1) reaches the same cell on a span of consecutive tabs in Excel and LibreOffice, but not in Google Sheets; and reaching a different FILE is a different mechanism again. Each section below was executed in LibreOffice Calc and shows what it actually returned, including the cases that error.

Sheet names with spaces or punctuation — wrap the name in single quotes

The bare SheetName!Cell form only works while the name is a single plain word. As soon as the tab is called Q1 Data, Profit & Loss or 2026 Budget, the name goes inside single quotes and the exclamation mark stays outside them: ='Q1 Data'!B2. The quotes wrap the sheet name only — never the cell reference. The runs below use a tab literally named Q1 Data holding Month in column A and Amount in column B: Jan 120 in row 2, Feb 80 in row 3, Mar 100 in row 4, with headers in row 1.

='Q1 Data'!B2

Quote the name whenever it contains a space or punctuation, whenever it starts with a digit, and whenever it could be mistaken for a cell reference; an apostrophe inside the name is escaped by doubling it, as row three shows. Excel and Google Sheets document the same quoting rule. The last row is the honest divergence: $'Q1 Data'.B2 is LibreOffice Calc's own documented quoted syntax, but LibreOffice keeps a formula-syntax setting per document (Tools > Options > Calc > Formula > Formula syntax), and a workbook that came in as .xlsx is parsed as Excel A1, where a dot is not a sheet separator — so that spelling came back #NAME? in our run while ='Q1 Data'!B2 returned 120 from the very same file. In a native .ods under Calc A1 syntax the dot form is the one that works and the exclamation mark is the one that fails. The practical rule: type whichever separator the app in front of you shows in its own formula bar, and let the file format carry it across — saving to .xlsx converts LibreOffice's dots to exclamation marks and opening one converts them back for display.

A whole range — or a whole column — from another sheet

A sheet prefix is not limited to one cell: any range argument inside any function can carry it, so =SUM(Data!B2:B6) totals a column that lives on the Data tab while the formula sits on your summary tab. Whole-column references work across tabs too ('Q1 Data'!B:B), which is the usual way to write a summary that keeps working as rows are added below. The runs below use the Q1 Data tab from the previous section plus a Data tab: Item / Units / Price headers in row 1, then apple 10 1.5, banana 20 0.5, cherry 30 3, date 40 2, elder 50 4 in rows 2 to 6.

=SUM(Data!B2:B6)

Full-column cross-sheet references execute normally: AVERAGE over 'Q1 Data'!B:B returned 100, the average of 120, 80 and 100, because the text header and the empty cells below are skipped rather than counted as zeros. The third row is the mistake worth internalising — =SUM(Data!B2:B6,C2:C6) is not an error and does not warn you; the second range has no prefix, so it points at C2:C6 of the sheet the formula is on. Ours was empty, so the answer came back 150 — 161 is what the fully-prefixed row directly beneath it returned. Prefix every range, every time. Excel and Google Sheets document the same behaviour for both the prefix rule and whole-column arguments; in LibreOffice Calc the same formulas take a dot when typed into a native document. A conditional total across tabs is the same idea with criteria attached — see the SUMIF-from-another-sheet recipe.

Build the reference from a cell value with INDIRECT

When the tab name should be data rather than something typed into the formula — a dashboard with a month picker, or one summary formula filled down against a list of tab names — assemble the reference as text and hand it to INDIRECT. Put A1's contents inside single quotes as you build the string, "'"&A1&"'!B2", and the formula keeps working when the tab name contains a space. The runs below read the Q1 Data tab described above; A1 on the current sheet holds the tab name.

=INDIRECT("'"&A1&"'!B2")

A1 holds the name of the tab to read. The referenced tab, Q1 Data, holds Jan 120 in B2, Feb 80 in B3 and Mar 100 in B4.

INDIRECT is documented identically in Excel and Google Sheets; in LibreOffice Calc the string it is given is parsed with the document's own formula syntax, so a workbook that arrived as .xlsx takes "'Q1 Data'!B2" (as executed here) while a document set to Calc A1 syntax takes the dot form — INDIRECT's optional second argument chooses A1 or R1C1 notation, not the sheet separator. Three costs come with the flexibility. INDIRECT is volatile: it recalculates on every change anywhere in the workbook, which is unnoticeable in one cell and painful in ten thousand. Its reference is a string, so nothing updates it — rename the tab, or insert a row above the target cell, and the formula keeps pointing at the old name and the old address instead of following the move the way a real reference does. And error handling behaves differently from what you would expect: with A1 naming a missing tab, wrapping the call in IFERROR still returned #REF! in LibreOffice 25.8.7.3 here, while ISERROR on the same call returned TRUE — so guard with IF(ISREF(...)) as the last row does, which returned the fallback text. Excel and Google Sheets both document IFERROR as trapping #REF!. For the month-switching dashboard pattern in full, see the recipe on using a cell value as a sheet name.

3-D references: the same cell on a span of consecutive tabs

First-sheet : last-sheet ! cell reaches one cell on every tab in that span at once. The runs below use three tabs sitting next to each other — Q1, Q2 and Q3 — with 100, 200 and 300 in A1 respectively.

=SUM(Q1:Q3!A1)

The 3-D form executed here exactly as Microsoft documents it for Excel: 600 and 200 across three tabs. The span is positional, not a list of names — drag a fourth tab between Q1 and Q3 and it joins the total automatically, drag one out and it drops off, which is why the pattern is usually written with empty bookend tabs. It only works with functions that accept several areas (SUM, AVERAGE, COUNT, MIN, MAX and friends); handing one to VLOOKUP returned #VALUE!. The dot spelling failed for the same reason as in the first section — native LibreOffice syntax, Excel-syntax document — while the exclamation-mark form ran fine in that file. Google Sheets documents no support for 3-D references at all: there you add the tabs explicitly, =Q1!A1+Q2!A1+Q3!A1. The dedicated recipe on summing the same cell across multiple sheets covers the roll-up pattern, the bookend-tab trick and the Sheets workaround in full.

Look a value up on another sheet (XLOOKUP and VLOOKUP)

A lookup that reads a table on another tab is an ordinary lookup with the sheet prefix on each range argument. The runs below use the Data tab — Item / Units / Price in row 1, then apple 10 1.5, banana 20 0.5, cherry 30 3, date 40 2, elder 50 4 — plus the Q1 Data tab, with the value being looked up in A2 of the current sheet.

=XLOOKUP(A2,Data!A:A,Data!C:C)

A2 holds the item to look up. The Data tab holds the lookup table: item names in A, units in B, prices in C, rows 2 to 6.

Two things to get right. Every range argument needs its own prefix — XLOOKUP takes two, and forgetting the second points it at the current sheet. And VLOOKUP's column number counts from the first column of the block you gave it, not from column A of the sheet: Data!A:C with index 3 is column C, whereas Data!B:C with index 3 would be off the end. Version support is the other catch. VLOOKUP is everywhere. XLOOKUP needs Excel 2021 or Microsoft 365, current Google Sheets, or LibreOffice Calc 24.8 or newer — the same formula run through our harness on LibreOffice 24.2.0.3 came back #NAME?, which is the recorded result in results/libreoffice-24.2.json; the runs on this page are LibreOffice 25.8.7.3. On older builds use INDEX/MATCH with the same prefixed ranges. The VLOOKUP-from-another-sheet and XLOOKUP-from-another-sheet recipes go into each one on its own.

What actually changes between Excel, Google Sheets and LibreOffice

Within one workbook the differences are small and mostly cosmetic; between files they are real. The two runs below are the same cell of the same Data tab, from the same .xlsx-derived workbook, written with each separator.

=Data!B2

Same-workbook references: Excel and Google Sheets document Sheet!A1 and nothing else; LibreOffice Calc documents Sheet.A1 as its own A1 syntax and offers Excel A1 and Excel R1C1 as alternatives, set per document, which is why the dot form returned #NAME? in the .xlsx-derived file above while the exclamation mark returned 10 from the same tab. Round-tripping handles the swap for you: save from LibreOffice to .xlsx and dots become exclamation marks, open an .xlsx in LibreOffice and it keeps working. Quoting rules for awkward names are the same in all three. 3-D references exist in Excel and LibreOffice and are documented as absent in Google Sheets. Cross-FILE references are where the three really part company, and none of these were executed here — they are the documented forms only. Excel documents ='[Budget.xlsx]Q1 Data'!A1 for an open workbook and a full path in single quotes for a closed one, refreshed through Data > Edit Links. LibreOffice Calc documents ='file:///home/you/Budget.xlsx'#$'Q1 Data'.A1, and its DDE and WEBSERVICE functions for live external data. Google Sheets has no file-path references at all: reaching another spreadsheet is IMPORTRANGE("url-or-key","'Q1 Data'!A1"), which needs a one-time access grant on the source file and has no Excel or LibreOffice equivalent — so a Sheets model built on IMPORTRANGE does not survive an export to .xlsx. Everything shown with a returned value on this page was executed in LibreOffice Calc 25.8.7.3 by our harness; the Excel and Google Sheets behaviour described here comes from each vendor's own documentation, because these recipe formulas were not run in those apps. (Our function-level Google Sheets verdicts elsewhere on the site ARE executed — see the methodology page; the recipe corpus is LibreOffice-only.)

Verified, not just documented

We ran =Summary!B2*Data!C5 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 60 — exactly the expected result. The 28 further formulas in the sections above were executed the same way, and the number shown beside each one is what LibreOffice actually returned — nothing on this page is a hand-typed result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 60 for the worked example, the same value LibreOffice produced. The 28 further formulas above were run through Sheets the same way and have their own column; 6 of them came back with a value different from LibreOffice’s, flagged in that column. 1 of the checks on this page is left out of the Google Sheets verdict entirely, because Google executed it against a workbook the LibreOffice run never saw — the value is still shown as it came back, marked not comparable, with the reason under the title. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

SUM · SUMPRODUCT · AVERAGE · COUNT · INDIRECT · IFERROR · IF · ISREF · VLOOKUP · XLOOKUP — see full Excel, Google Sheets & LibreOffice compatibility for each.

Related recipes

• How to VLOOKUP from another sheet
• How to XLOOKUP from another sheet
• How to SUMIF from another sheet
• How to sum the same cell across multiple sheets (3D reference)
• How to use a cell value as a sheet name (INDIRECT)

Related comparisons

• AVERAGE vs AVERAGEA (and COUNT vs COUNTA): the A matters
• AVERAGE vs MEDIAN: which 'typical value' to trust
• COUNT vs COUNTA: numbers only, or everything?