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.

Sherri wrote:

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?

--

Dave Peterson