Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell contents from a reference | Excel Discussion (Misc queries) | |||
How do I make a cell Flash on and off it's contents? | Excel Worksheet Functions | |||
How do I make the contents of a cell appear to flash? | Excel Worksheet Functions | |||
Make cell contents a reference | Excel Discussion (Misc queries) | |||
Reference cell contents | Excel Discussion (Misc queries) |