What Excel function constructs and returns a reference to a range of cells?

Question

I am already familiar with Excel's address function, which takes two integers representing a row and a column and returns a reference to the specified cell. For example, =address(2,4) gives $d$2. Is there a built-in Excel function that does for cell ranges what address does for individual cells -- something like =func(2,4,6,8) or =func(address(2,4),address(6,8)) which can return $d$2:$h$6?

These are my failed attempts at a workaround so far:

• =address(2,4):address(6,8): The syntax is invalid.
• =address(2,4)&":"&address(6,8): There is no syntax error, but because Excel treats the return value of this formula as a string, it is not usable by other functions as a range reference. For example, =countif(address(2,4)&":"&address(6,8),">10") does not actually give the same result as countif($d$2:$h$6,">10").

Answer 1

You can use INDIRECT :

=countif(INDIRECT(address(2,4)&":"&address(6,8)),">10")

Answer 2

You can also use the offset function:

=COUNTIF(OFFSET(D2,0,0,4,3), ">10")

defines a range starting at D2 that is 4 rows and 3 columns. The two 0's can be used to move the start location from D2, so entering D2,1 will move to D3 etc...

Answer 3

If you are going to dynamically populate the parameters in Ron Rosenfeld's answer using calculations, you can avoid the usually obnoxious change from a column's number to its letter by using the following approach:

=INDEX(2:2,,4):INDEX(6:6,,8)

which gives the same D2:H6 but uses a number for the column rather than a letter in the parts of the range.

(And although there is a simple way to get the column letter, no one seems to know it and defaults to the obnoxious divisions by 26 and so on. Thing is, the easy way STILL ivolves adding a bit while the above goes at it pretty directly.)

Answer 4

A problem with the commonly used INDIRECT and/or OFFSET methods is that they are both volatile functions. That means they recalculate whenever the worksheet is recalculated, even if such would not be necessary. On a large worksheet with many such formulas, this can be time consuming.

You may be better off using the non-volatile INDEX function:

=INDEX($D:$D,2):INDEX($H:$H,6)

will return a reference to D2:H6

Note Something you need to be cognizant of:

The various formulas provided will react differently to insertion and deletion of rows or columns in or before the affected ranges. In some cases, they will continue to refer to the original range at its new location; in others they won't.