SHEETOFFSET

SHEETOFFSET(reference, sheet_offset)
It allowed you to reference a cell on a different worksheet by specifying an offset number, rather than hard-coding the sheet name.
The value from the same cell address on the sheet that is N sheets away.

reference = a cell reference on the current sheet
sheet_offset = how many sheets to move (positive = right, negative = left)

Offset is relative to the sheet containing the formula, Not the sheet containing the reference argument.
Works with positive and negative offsets
If you pass a range, it returns a range from the offset sheet.
Dynamic - If you reorder sheets, the function updates automatically.

No Equivalents
INDIRECT("'SheetName'!A1") - but requires hard-coded sheet names
3-D formulas like =SUM(Jan:Dec!B5) - but only work for aggregations, not retrieving a single cell