# Define a range using a formula

A simplified version of my problem is that on a different sheet called `Sheet2` I have data that I want to average:

``````=AVERAGE(Sheet2!\$A\$1:Sheet2!\$A\$100)
``````

Depending on where the code above is used I need to exchange column `A` for another letter, for example column `D`. To do this I have created a function:

``````=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)
``````

which will output `D`.

My problem is that instead of having to write:

``````=AVERAGE(Sheet2!\$D\$1:Sheet2!\$D\$100)
``````

I want to use:

``````=AVERAGE(Sheet2!\$MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)\$1:Sheet2!\$MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)\$100)
``````

but Excel does not accept this. How can I use functions next to `Sheet2!`?

• This could be done with `INDIRECT`, but there may be a better way. What determines which column you want to use? – cybernetic.nomad Oct 16 at 16:16
• As I move to the right in my spreadsheet I want to increase by increments of 3, i.e. the first Average uses column A, second Average uses column D(=A+3), third Average uses column G (=A+3+3) etc – litmus Oct 16 at 16:25

You can use INDIRECT :

`=AVERAGE(INDIRECT ("Sheet2!\$" & MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)&"\$1:Sheet2!\$" & MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)&"\$100"))`

• Thank you, Máté! This is exactly what I needed help with, I would never have figured out that its possible to use quotation marks in this manner. Thanks!! – litmus Oct 16 at 18:42
• There is a useful Excel function that simplifies and strengthens this approach, called ADDRESS(row, column,[abs],[format],[sheet]) that produces the cell reference as text. For example, the output of ADDRESS(30,77,,,"Sheet2") is "Sheet2!\$BY\$30" The benfits of using this include: (1) formula that is short, simple, fast and understandable (2) handling columns > "Z", and (3) coping with localisation issues. So you can use INDIRECT( ADDRESS(.....) & ":" & ADDRESS(....) ) – Duke Bouvier Oct 21 at 20:22

OFFSET and INDIRECT are volatile we can use INDEX

``````=AVERAGE(INDEX(Sheet2!\$A\$1:\$Z\$100,0,4))
``````

The `0` tells the INDEX to return all the rows and the `4` is the 4th column. So all the rows from 1 to 100 in column 4 will be averaged.

``````=AVERAGE(INDEX(Sheet2!\$A\$1:\$Z\$100,0,(COLUMN(A:A)-1)*3))
``````=AVERAGE(OFFSET(Sheet2!\$A:\$A,0,(COLUMN()-1)*3))
Note that `OFFSET` is volatile and will increase the calculation load in your workbook