Building a Data Summary Page with the INDIRECT() Function

Hello, I'm new to the forum and hoping someone can help me.

I'm trying to agregate data across monthly entries in a way that is easy to update with each new month.

For example, the display cell would contain =AVERAGE(INDIRECT("R14")). R14 would then contain '2021-04:2020-07'!E5, where the two yyyy-mm dates are the names of the sheets within the file. I've also tried directly referencing the cell as both a string and a value. Each throws a different error.


=AVERAGE(INDIRECT("R14")) ==> #DIV/0!
=AVERAGE(INDIRECT(R14)) ==> #REF!
=AVERAGE(R14) ==> #DIV/0!
=AVERAGE("R14") ==> #VALUE!

Is this even a valid method or am I up the creek? Is there a simpler way to accomplish the same thing? Ideally, I'd like to get summary data over the whole set and over the past year.

Thank you!

The first one in your list is the way to do it. It gives a #DIV/0! error if the cell pointed to is empty, as there are no cells which contribute to the result. You can avoid that by doing this:

=IFERROR(AVERAGE(INDIRECT("R14")),"")

which will return a blank instead.

Hope this helps.

Pete

Thanks Pete,

I think my initial explanation was incomplete. There are enough number values in the referenced cells for the mathematical functions to work. The funciton =AVERAGE('2021-04:2020-07'!E5) returns the correct number.

The problem only shows up when I try to drop in the INDIRECT().

The INDIRECT function will not work with 3D reference formulae, I'm afraid.
A workaround would be to split the range string in two cells, say 2021-04 in R14 and 2020-07 in S14.
Then use this formula:

Formula:

Please Login or Register  to view this content.

Good luck!