Hi bj,

thanks for the response.
However, this keeps giving me a =REF! error when I enter it.

Stepping through the function helps shows that the interim step right before
the =REF! value has the equation =INDIRECT( "TeeJay!$L$40") (the italised
writing is making it hard to determine if those are quotation marks or two '
marks together)
The ' marks to reference the TeeJay sheet seem to disappear on the step
before.

I have looked at Excel help and to be honest I'm not finding it helpful for
INDIRECT formulas.

One thing I realise now I forgot to mention was that cell A1 on the TeeJay
worksheet (the one that cell K1 on the current sheet is referencing) has this
formula in:
=MID(CELL("filename",A1),(FIND("]",CELL("filename",A1)))+1,50)

I don't think this is affecting the INDIRECT formula but I haven't used the
MID command before picking it up elsewhere in the discussion group

I can tell that I'm not the only person to have had problems with INDIRECT
formula but I haven't found yet a reply in these groups that seems to address
this particular issue.

Thanks in advance to any help
"bj" wrote:

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