For a particular template I needed to get the maximum value of a column of numbers in one of the Data worksheet's named ranges. The problem I ran into was that I didn't know at what row the named range would start, and how many rows it would have, because each time the Data worksheet generates, the various named ranges could have a different number of rows.
Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
Figure 1 shows a simple example of using INDIRECT to get a MAX value from a range of column values:
But what if you didn't know what the column's start and end row numbers were going to be, but you did know that they'd be encompassed in a named range? That's what I was faced with. I knew the name of the named range that contained the values. I also knew what column the values would be in. What I did not know were the first and last row numbers of the range. The solution I found was the INDIRECT function. (If you know a better way, please comment!)
If the named region you need to reference is on a different worksheet (for example, a worksheet named "Data"), simply include the name of the worksheet when concatenating the reference string, like this: