Rob Hick wrote...
In response to Harlan - the named formula 'WorksheetName' returns the
name of the sheet on which the named formula is used. In this
instance
it returns the name of the 'Summary' sheet - becuase that is actually
where the data element I want to look up is located (it's a month, e.g
April 05).

There are two common problems that could occur with the formula you use
to define WorksheetName, which is

=3DRIGHT(CELL("file=ADname"),LEN(CELL("filename"))-F=ADIND("]",CELL("filena=
me")))

1=2E CELL("filename") without a second argument will return the worksheet
name for the active cell as of the most recent recalc, not the
worksheet name for the calling cell.

2=2E If you haven't yet saved the file, CELL("filename") will return an
error.

This is why I wanted you to check the value of WorksheetName. If you
have, then no problem. If you haven't and are just assuming that the
formula couldn't possibly be wrong, then you've made the most
fundamental mistake in programming: never assume anything.

And the strange mathematics exists because the second 2 is actually
another named formula in reality - it was just easier to put 2 rather
than define another in the post.

So your actual formula looks more like

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

[If you want to provide a simplified example, then eliminate *ALL*
noise, so MysteryDefinedName+ROW()+2 - ROW() rather than 2+ROW()+2.]

Are you sure it isn't this other defined name that's causing the
problem?

As defined above, WorksheetName must be the name of a worksheet in the
active workbook, so the INDIRECT call should work as long as 2+ROW()+X
evaluates to an integer between 1 and 65536, you're recalculating from
within the same workbook, *AND* you have Transition Formula Evaluation
*DISabled* [otherwise, you need to use TEXT(2+ROW()+X,"0")].

This points out a necessary debugging step. Enter the following
formulas.

X97:
=3DW=ADorksheetName&"!$A"&2+ROW()+X

X98:
=3DINDIRECT(X97)

X99:
=3DVLOOKUP(X98,London!data,2,0)

This would isolate the expression in which the error occurs.