ISREF Function (LibreOffice Calc)
The ISREF function in LibreOffice Calc checks whether a value is a valid cell reference. It is essential for validating dynamic references, INDIRECT formulas, and advanced lookup logic.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the ISREF Function Does ▾
- Returns TRUE if a value is a valid cell reference
- Returns FALSE for text, numbers, logical values, errors, or empty cells
- Useful for validating dynamic references
- Works with direct references and reference-returning formulas
It is designed to be precise, safe, and essential for dynamic spreadsheets.
Syntax ▾
ISREF(value)
Arguments
- value:
Any value, expression, or cell reference.
Basic Examples ▾
Check if A1 is a reference
=ISREF(A1)
Returns TRUE.
Check if a literal string is a reference
=ISREF("A1")
Returns FALSE — text is not a reference.
Check if a number is a reference
=ISREF(42)
Returns FALSE.
Check if a formula returns a reference
=ISREF(INDIRECT("A1"))
Returns TRUE.
Advanced Examples ▾
Validate dynamic references created with INDIRECT
=ISREF(INDIRECT(A1))
TRUE if A1 contains a valid reference string.
Check if ADDRESS output is a valid reference
=ISREF(INDIRECT(ADDRESS(1;1)))
Always TRUE.
Validate references before using them
=IF(ISREF(INDIRECT(A1)); INDIRECT(A1); "Invalid reference")
Detect whether a named range exists
=ISREF(INDIRECT("MyRange"))
TRUE if MyRange is defined.
Check if a range reference is valid
=ISREF(A1:A10)
TRUE for any valid range.
Validate references in lookup formulas
=IF(ISREF(A1); VLOOKUP(B1; A1; 2; 0); "Bad range")
Check if OFFSET returns a valid reference
=ISREF(OFFSET(A1; 1; 0))
TRUE unless OFFSET goes out of bounds.
Edge Cases and Behavior Details ▾
Text that looks like a reference is NOT a reference
=ISREF("B2")
Returns FALSE.
INDIRECT converts text → reference
=ISREF(INDIRECT("B2"))
Returns TRUE.
Blank cells are NOT references
=ISREF(A1)
Returns FALSE if A1 is empty.
Errors are NOT references
=ISREF(#REF!)
Returns FALSE.
A formula returning a reference IS a reference
=ISREF(OFFSET(A1; 0; 1))
Returns TRUE.
A formula returning a value is NOT a reference
=ISREF(A1 + 1)
Returns FALSE.
Common Errors and Fixes ▾
ISREF returns FALSE unexpectedly
Cause:
- Value is text, not a reference
- INDIRECT is missing
- Reference is out of bounds
- Named range does not exist
Fix:
Ensure INDIRECT or ADDRESS is used to convert text into a reference.
ISREF returns TRUE unexpectedly
Cause:
- Formula returns a reference even if the reference is empty
- OFFSET or INDEX returns a reference, not a value
ISREF used on a range incorrectly
=ISREF(A1:A10)
Returns TRUE — ranges are valid references.
Best Practices ▾
- Use ISREF to validate dynamic references before using them
- Use INDIRECT to convert text into references
- Use ADDRESS to generate references programmatically
- Use ISREF inside IF to prevent #REF! errors
- Validate user input when building reference-driven models
Related Patterns and Alternatives ▾
- Use INDIRECT to convert text → reference
- Use ADDRESS to generate references
- Use ISERROR to detect invalid references
- Use TYPE to inspect value types
- Use IF to build safe reference logic
By mastering ISREF and its companion functions, you can build flexible, dynamic, and error‑resistant spreadsheets in LibreOffice Calc.