Dynamic reference to sheet name
 

How can I insert a variable or cell value into a function to make the
reference (or worksheet) dynamic?

Imagine that there are four worksheets named: Summary, apples, oranges and
bananas. On the Summary sheet the first column contains one of the other
worksheet names, eg all values in column A contain one of the worksheet
names: apples, oranges or bananas. Cells in other columns contain references
that leverage the worksheet names in column A. For instance, something like
the following:
=average(<A1!B1:B30)
=average(<A2!B1:B30)

My question is, what should I replace <A1 with such that it substitutes in
the value from the first column which will match one of the worksheets in the
workbook?

Darien0104 wrote:
How can I insert a variable or cell value into a function to make the
reference (or worksheet) dynamic?

Imagine that there are four worksheets named: Summary, apples, oranges and
bananas. On the Summary sheet the first column contains one of the other
worksheet names, eg all values in column A contain one of the worksheet
names: apples, oranges or bananas. Cells in other columns contain references
that leverage the worksheet names in column A. For instance, something like
the following:
=average(<A1!B1:B30)
=average(<A2!B1:B30)

My question is, what should I replace <A1 with such that it substitutes in
the value from the first column which will match one of the worksheets in the
workbook?

=AVERAGE(INDIRECT("'"&A1&"'!B1:B30"))