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: