EXCEL: Reference Functions
- The OFFSET is a powerful function in excel that returns a cell or range of cells that is specified by 5 inputs: reference or starting point, row offset, column offset, optional height in rows, optional width in columns.
=OFFSET(reference, rows, columns, [height],[width])

To get the latest value,
=OFFSET(A1, 3, 1, 1, 1)
2. The INDIRECT function will convert a text string into a valid reference. We can use & operator to create valid strings.
=INDIRECT(A9&”!G6"), A9 can be any text value -“California”