check out the indirect() function in help.
If I am reading your input right
=indirect(K1&"!$L$40")
but put only TeeJay in K1

depending on your sheet names you may have to add the ' to the front and
back on the sheet name
"TeeJay" wrote:

On one sheet I have the formula ='TeeJay'!$L$40 where TeeJay is the name of a
different sheet to the one I'm working on but in the same workbook.

Is it possible to change the formula so that it refers to a cell on the
current sheet that has a reference to the other sheet?

If I had ='TeeJay'!A1 (where A1 has the name of the sheet) in cell K1 on the
current sheet, would it be possible to get a formula that works =K1!$L$40

If I try this at the minute, I get a dialog box to update values. How should
I be writing this formula?

I'm using Excel 2000 and would prefer not to use VBA (some of the computers
this will have to be viewed on have macros disabled).

Many thanks for any help