Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a cell value as the ref argument in CELL"contents" form
C1 to U1 contain the formulae =CELL("contents",'Dec08'!B$41) where Dec08 is
the name of another sheet in the same workbook. (Of course, each cell betwen C1 and U1 refers to consecutive cells in row 41 in the Dec08 sheet.) I want to be able to add more month sheets (ex: Jan09) over time and create another line of cell references wihout having to manually set each new cell to the new month sheet. I've tried entering the sheet name in X1 and then refering to its contents in the Cell"contents" formulae, but the function always returns a error. I was unable t o use CONCATENATE or ADDRESS successfully. Help please. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a cell value as the ref argument in CELL"contents" form
Why are you even using CELL in the first place
=Dec08!$B$41 will return what's in B41 in sheet Dec08 If you have the sheet name in X1 then you can use to get what's in B41 =INDIRECT("'"&X1&"'!B41") note that if you put Jan08 in X1 you need to format the cell as text before entering the sheet name or else you would need to change the formula to =INDIRECT("'"&TEXT(X1,"mmmyy")&"'!B41") because if you enter Jan089 in a cell it will convert it to a date serial number so it is better to format the cell as text first -- Regards, Peo Sjoblom "PFB" wrote in message ... C1 to U1 contain the formulae =CELL("contents",'Dec08'!B$41) where Dec08 is the name of another sheet in the same workbook. (Of course, each cell betwen C1 and U1 refers to consecutive cells in row 41 in the Dec08 sheet.) I want to be able to add more month sheets (ex: Jan09) over time and create another line of cell references wihout having to manually set each new cell to the new month sheet. I've tried entering the sheet name in X1 and then refering to its contents in the Cell"contents" formulae, but the function always returns a error. I was unable t o use CONCATENATE or ADDRESS successfully. Help please. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a cell value as the ref argument in CELL"contents"
Thanks! I didn't know about INDIRECT. I knew there had to be a simpler way.
"Peo Sjoblom" wrote: Why are you even using CELL in the first place =Dec08!$B$41 will return what's in B41 in sheet Dec08 If you have the sheet name in X1 then you can use to get what's in B41 =INDIRECT("'"&X1&"'!B41") note that if you put Jan08 in X1 you need to format the cell as text before entering the sheet name or else you would need to change the formula to =INDIRECT("'"&TEXT(X1,"mmmyy")&"'!B41") because if you enter Jan089 in a cell it will convert it to a date serial number so it is better to format the cell as text first -- Regards, Peo Sjoblom "PFB" wrote in message ... C1 to U1 contain the formulae =CELL("contents",'Dec08'!B$41) where Dec08 is the name of another sheet in the same workbook. (Of course, each cell betwen C1 and U1 refers to consecutive cells in row 41 in the Dec08 sheet.) I want to be able to add more month sheets (ex: Jan09) over time and create another line of cell references wihout having to manually set each new cell to the new month sheet. I've tried entering the sheet name in X1 and then refering to its contents in the Cell"contents" formulae, but the function always returns a error. I was unable t o use CONCATENATE or ADDRESS successfully. Help please. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use a cell value as the ref argument in CELL"contents" form
You probably want the INDIRECT function.
-- David Biddulph "PFB" wrote in message ... C1 to U1 contain the formulae =CELL("contents",'Dec08'!B$41) where Dec08 is the name of another sheet in the same workbook. (Of course, each cell betwen C1 and U1 refers to consecutive cells in row 41 in the Dec08 sheet.) I want to be able to add more month sheets (ex: Jan09) over time and create another line of cell references wihout having to manually set each new cell to the new month sheet. I've tried entering the sheet name in X1 and then refering to its contents in the Cell"contents" formulae, but the function always returns a error. I was unable t o use CONCATENATE or ADDRESS successfully. Help please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert cell "contents" into a "comment" | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
how to use "if" logical argument to format/copy some pic. in cell | Excel Worksheet Functions | |||
how to increase size of "name box" and "contents of cell " displa. | New Users to Excel | |||
if one cell has "X" textin contents, then pull cell "Y"... | Excel Worksheet Functions |