Define a range using a formula

Question

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!?

Answer 1

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.

As per your comments:

=AVERAGE(INDEX(Sheet2!$A$1:$Z$100,0,(COLUMN(A:A)-1)*3))

Answer 2

Here is one way:

=AVERAGE(OFFSET(Sheet2!$A:$A,0,(COLUMN()-1)*3))

Note that OFFSET is volatile and will increase the calculation load in your workbook

Answer 3

You can use INDIRECT :

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