0

I have worksheets named "Team 1", "Team 2", etc. Here's now I currently get my data:

=AVERAGE('Team 1'!D12:D14)
=AVERAGE('Team 2'!D12:D14)
=AVERAGE('Team 3'!D12:D14)
...
=AVERAGE('Team 1'!D15:D16)
=AVERAGE('Team 2'!D15:D16)
=AVERAGE('Team 3'!D15:D16)
...

And so on. How do I repeat this for a large number of worksheets without manually editing the 1, 2, 3 etc. in each formula?

Improve this question

1 Answer 1

Reset to default
2

There may be a better way to do this, but I found a workaround. First I put the names of each worksheet into a series of cells:

Team 1
Team 2
Team 3
...

Then I used an INDIRECT reference and string concatenation:

=AVERAGE(INDIRECT("'"&B4&"'!"&"D16:D17"))

Where B4 contains the text Team 1, B5 contains Team 2, etc. With this formula, everything except the B4 is a string, so I can click-drag and Excel will auto increment it to B5, B6, and so on.

Improve this answer
1
  • 2
    You can remove the "&" as from the '! until the end is one string. You can also do =AVERAGE(INDIRECT("'Team "B4&"'!D16:D17")) and have in B4 only the number.
    – Yisroel Tech
    Commented Jun 23, 2023 at 19:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .