I’ve been working with LibreOffice Calc (or Excel) spreadsheets recently and wanted to share some of the things I’ve learned.
Absolute vs Relative Cell References
The main difference between referencing a cell relatively vs absolute is that the absolute reference includes a
$ in the front. This is important if you want to drag a formula across multiple cells.
Referring to a sheet name
To refer to cells in another sheet, first begin the reference with the sheet name, then a period, follow by the cells you wish to reference in that sheet. If the sheet name has spaces in it, then you need to wrap it in quotes.
Referring to a column
To refer to a single column, you need to repeat the column name separated by a colon.
Get row that matches a query
For this we’ll use the
MATCH function. It takes three parameters:
- The value to match
- The range of cells to query over
- Which comparison function to use. Use
It will then return the first row number that matches the query.
MATCH("Bob", B:B, 0)
Query a value based on another from that row.
To do this, we will need to combine both the
INDEX function and the
MATCH function. The
INDEX function takes three parameters:
- The range of cells to reference
- The row number
- The column number
MATCH function as the second argument, and you can reference another column of a row based on a query.
INDEX(A:B, MATCH("Bob", B:B, 0), 1)
Refer to a value in a nearby cell
OFFSET function you can refer to a cell relative to another. Its three parameters are:
- Reference Cell
- Row Offset
- Column Offset
# To see the value in the row above A5 (A4) OFFSET(A5, -1, 0, 1, 1)
Strings separated by
& are concatenated together.
"Hello " & "World."