Rob Hick wrote...
thanks for all the pointers Harlan. I'd misunderstodd what the
CELL("filename") function returned so I'm not sure that my
'WorksheetName' name is actually going to always return what i
require.
Is there any other funtion that will achieve what I'm after?
..=2E.

If you're using CELL("Filename") in a defined name, and you want it to
return the workbook/worksheet name of the calling cell, then change it
to

CELL("Filename",INDIRECT("A1"))

When that defined name appears in a cell formula, Excel will evaluate
it in the context of that cell, in which case INDIRECT("A1") will
resolve to the A1 cell in the same worksheet as the calling cell.

. . . but I tried your sugestion
of TEXT() just in case nad it didn't solve the problem.

That was a shot in the dark in case you had Transition Formula
Evaluation enabled. Unlikely, but a possibility.

The problem with trying to isolate the expression where the error
occurs is that the error doesn't occur if i reference the name
directly, i.e. =3DLondon!MonthSales; the problem only comes if i
reference it using the INDIRECT function to 'build' the reference text
from data in other cells.

Your original post showed that you were defining London!MonthSales as

=3DVLOOKUP(INDIRECT(W=ADorksheetName&"!$A"&2+ROW() +2),=ADLondon!data,2,FALS=
E)

Are you saying that if you enter that formula exactly in a cell that
you get the result you want, but if you enter

=3DLondon!MonthSales

in the cell you don't? Or do you mean that if, say, cell X99 contained

London!MonthSales

that the formula

=3DINDIRECT(X99) [or =3DINDIRECT("London!MonthSales")]

returns a #REF! error? If the latter, London!MonthSales refers to a
formula that returns a value rather than a range reference. INDIRECT
can only return range references, so it can't return the value given by
London!MonthSales. You could fix this by defining London!MonthSales as

=3DINDEX(=ADLondon!data,MATCH(INDIRECT(W=ADorkshee tName&"!$A"&2+ROW()+2),
INDEX(=ADLondon!data,0,1),0),2)

INDEX, in this case, returns a range reference rather than a value, so
INDIRECT("London!MonthSales") would be able to return a range
reference, which would be resolved to its value.