Use cell contents to make formula reference

[Sherri]

Is there a way to use the contents of a cell to substitute for a portion of a
cell reference?

Example:
I have worksheets named 1, 2, 3. I would like a summary page to evaluate
either sheet 1, sheet 2 or sheet 3 for a given set of variables.

Instead of hard coding the sheet.cell reference in the formula on the
summary page, I would like to use cell $a$1 as a variable to modify the
formula. Can I write the formula to calculate (for example, an average) any
sheet based on the sheet name I type in cell A1. So, insted of the formula

=Average(1!A4:F4)

I would like to use cell A1 as the place I store the variable. (The
variable being the sheet name "1"). Theoretically, that would mean my
formula would look something like

=Average($A$1!A4:F4)

Is anything like this possible?

 

[Project Mangler]

Sherri,

=AVERAGE(INDIRECT(A1&"!A4:F4"))

 

[Project Mangler]

Or more accurately since you only want "1" in the cell

=AVERAGE(INDIRECT("Sheet" & A1 &"!A4:F4"))

 

[Dave Peterson]

Sometimes, you need to surround the sheet name with apostrophes.

=Average(indirect("'" & $A$1 &"'!A4:F4"))

If you don't need them, it won't hurt. But if you do need them, er, you need
them.

 

[ozgrid.com]

RE: Sometimes, you need to surround the sheet name with apostrophes

Yes, if the sheet tab name has a space character.