How to use a cell value as the ref argument in CELL"contents" form

[PFB]

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.

 

[Peo Sjoblom]

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]

Thanks! I didn't know about INDIRECT. I knew there had to be a simpler way.

 

[David Biddulph]

You probably want the INDIRECT function.