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

P

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.
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top