How can I average across sheets defined by an INDIRECT value?

Question

I have 4 sheets in this sample, each with a value in A1, as follows:

• Sheet1 - A1=18
• Sheet2 - A1=15
• Sheet3 - A1=197
• Sheet4 - A1=534

Then in Sheet5 I have:

• B1='Sheet1
• B2='Sheet4

I want an average of A1 cells from Sheet1 to Sheet4, and I can accomplish this easily with the formula =AVERAGE(Sheet1:Sheet4!A1) in Sheet5!A1.

My problem is I want to do that same formula but referencing the sheet values in B1 and B2. I think I need INDIRECT for this, so I tried =AVERAGE(INDIRECT(B1 & ":" & B2 & "!A1")). When I enter that, though, I get a #REF! error. What could Excel be failing to reference?

Answer 1

Try this formula, Workbook

=AVERAGE(INDIRECT("'"&"Sheet"&ROW(INDIRECT(SUBSTITUTE($B$1,"Sheet","")&":"&SUBSTITUTE($B$2,"Sheet","")))&"'!"&CELL("address",A1)))