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

Time:02-19

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?

CodePudding user response:

Try this formula, AVERAGE_FROM_MULTIPLE_SHEETS

  • Related