Rob Hick wrote...
..=2E.
I tried reverting to the INDEX style syntax (great tip - thanks) but
unfortunately this doesn't work either - still getting a #REF! error.
What is even more strange is that putting
=3DCELL("address",London!MonthSales) returns the correct cell reference
as you say....?
..=2E.

There's a ROW() call inside your INDIRECT call. That could be a
problem. Very esoteric, but I should have caught it before. Anyway, try
defining London!MonthSales as

=3DINDEX(=ADLondon!data,MATCH(INDI=ADRECT(W=ADorks heetName&"!$A"&2+SUM(RO=
=ADW())+2),

INDEX(=ADLondon!data,0,1),0),2)

That is, replace the ROW() call with SUM(ROW()). While this seems odd
and unnecessary, ROW() always returns arrays, and this converts the
single entry array to a scalar/nonarray single number.


I tried putting =3DINDIRECT("WorksheetName") in a cell and it returns
the
#REF! error - could this be the problem?
..=2E.

This is correct and expected. Worksheet returns a text string, so
INDIRECT couldn't return a range reference.