trying to retrieve cell TEXT not formula

Hi,

I am looking for a way to refererence the text of another cell, not the formula that it contains.

I have a workbok that has 5 worksheets. On the first worksheet, a certain cell (lets say A1 for simplicty) uses the function:UPPER(MID(CELL("filename"),( FIND("]",CELL("filename"))+1),50))
to retrieve the title of the worksheet. (ie, A1 now serves as a title cell, matching the name of the worksheet).

My problem is, on the following four worksheets, I also want A1 to reflect the contents of the first worksheet's A1. I've tried using =Worksheet!A1, and =CELL("contents", Worksheet1!A1), and so forth, but my problem is, it reflects the formula of A1, not the returned value of the formula (the title). Initially, it works, but if I recalculate the cells on the four worksheets, it changes them all to the title of the current worksheet, not the first worksheet.

Is there a formula that I can use to retrieve the TEXT and not the formula?

Thanks

JP

I don't want to show Sheet1 on sheet 1, Sheet2 on Sheet 2, and so forth, I'm looking to show the title of Sheet1 on ALL sheets (Sheet1!A1, Sheet2!A1, Sheet3!A1, etc).

Chippy,

thanks! Oddly enough, adding the cell number in the initial formula makes a difference. Regardless of why, it works! Thanks!

John