Mutiple sheets average to last sheet using indirect

[MichelGagnon]

I have multiple sheets all named with a year. I have a last sheet called average.
I have found a way to average all the years into an exact same table on that last sheet.

=AVERAGE('2016:2020'!B4)

Now I am trying to use indirect to get the value of two cells so that I can easily change the years calculated. Now the formula that I have found only averages the first and last year.

=AVERAGE(INDIRECT("'"&M27:M28&"'!"&"B4"))

What am I missing?

 

[MichelGagnon]

I have multiple sheets all named with a year. I have a last sheet called average.
I have found a way to average all the years into an exact same table on that last sheet.

=AVERAGE('2016:2020'!B4)

Now I am trying to use indirect to get the value of two cells so that I can easily change the years calculated. Now the formula that I have found only averages the first and last year.

=AVERAGE(INDIRECT("'"&M23:M24&"'!"&"B4"))

What am I missing?

I thought that it should be this way:

=AVERAGE(INDIRECT("'"&M23&":"&M24&"'!B4"))

But I get an Invalid Cell reference number.

 

[MichelGagnon]

In case it helps anyone, I have found this formula on redit:

=AVERAGE(SUMIF(INDIRECT("'"&ROW(INDIRECT($M$23&":"&$M$24))&"'!B4"),">0"))