Using OFFSET with a 3D range (range across multiple sheets)

Question

I have a database with the relevant data in the same rows across several sheets, with data in every other column. I have been trying to use 3D ranges with AVERAGE/SUM and OFFSET but I can't seem to figure it out. I am getting a #VALUE error during the OFFSET part. Here is the setup I have been using.

=AVERAGE(OFFSET('Sheet 1:Sheet 4'!A1,0,COLUMN(A1)*2-1))

The sheet names do have spaces in them so the space in my above formula between the word Sheet and the number is intentional. I have tried the 3D range with just the average/sum functions and it works fine. Are the 3D ranges not compatible with the OFFSET function?

Domenic Domenic · Accepted Answer · 2017-08-22T03:21:17

Try...

=AVERAGE(N(OFFSET(INDIRECT("'"&{"Sheet1","Sheet2"}&"'!A1"),0,COLUMN(A1)*2-1)))

or

=AVERAGE(N(OFFSET(INDIRECT("'"&$G$2:$G$3&"'!A1"),0,COLUMN(A1)*2-1)))

...where G2:G3 contains the sheet names. Note that the second formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

FIRST EDIT

While an array formula is still required, you can avoid having to confirm the formula with CONTROL+SHIFT+ENTER, and you can avoid having to list your sheet names in a range of cells.

1) First define the name SheetNames as follows...

Refers to:  ={"Instrument Partners","Supply Partners","Repair Partners","Wholesale Partners"}

2) Then try...

=AVERAGE(INDEX(N(OFFSET(INDIRECT("'"&SheetNames&"'!A1"),0,COLUMN(A1)*2-2)),0))

...which only needs to be confirmed with ENTER.

SECOND EDIT

"'"&SheetNames&"'!A1" returns the following array of text values...

"'Instrument Partners'!A1"
"'Supply Partners'!A1"
etc...

This array of text values is passed to INDIRECT, which returns the following array of references...

'Instrument Partners'!A1
'Supply Partners'!A1
etc...

In turn, this array of references is passed to OFFSET, which also returns an array of references based on the row and column offsets...

OFFSET('Instrument Partners'!A1,0,COLUMN(A1)*2-2) --> 'Instrument Partners'!A1
OFFSET('Supply Partners'!A1,0,COLUMN(A1)*2-2) --> 'Supply Partners'!A1
etc...

Then we retrieve the values by passing this array of references to the N() function...

N('Instrument Partners'!A1) --> returns actual value from the cell reference
N('Supply Partners'!A1) --> returns actual value from the cell reference
etc...

Then we pass this array of values to the INDEX function, which returns an array of values and allows the formula to be confirmed with just ENTER, instead of CONTROL+SHIFT+ENTER.

Lastly, the array of values is passed to the AVERAGE function, which returns the actual average.

Using OFFSET with a 3D range (range across multiple sheets)

1 Answers