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.

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.

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.

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.