EXCEL: Reference Functions

1. 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”