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?
>Solution :
Try this formula,
=AVERAGE(INDIRECT("'"&"Sheet"&ROW(INDIRECT(SUBSTITUTE($B$1,"Sheet","")&":"&SUBSTITUTE($B$2,"Sheet","")))&"'!"&CELL("address",A1)))
