Use cell contents to make formula reference
 

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?

Use cell contents to make formula reference
 

Sherri,

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


"Sherri" wrote in message
...
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?

Use cell contents to make formula reference
 

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

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


"Sherri" wrote in message
...
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?

Use cell contents to make formula reference
 

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

Use cell contents to make formula reference
 

Sometimes, you need to surround the sheet name with apostrophes

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



--
Regards
Dave Hawley
www.ozgrid.com
"Dave Peterson" wrote in message
...
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