Imagine you want to refer to an array in Excel dynamically.
You can do it by using a formula like this:
It supposes that cells E1 and E2 contains the row and column number respectively for the start cell, and E3 and E4 the same for the end cell.
ADDRESS returns the cell address given the row and column number as parameters, while INDIRECT allows to take a cell selection based on its name.
=INDIRECT(ADDRESS(E1, E2) & ":" & ADDRESS(E3, E4))