Rob Hick wrote...
ok. tried putting the SUM(ROW()) in but still didn't fix the
problem - using INDIRECT(...) still returns #REF!
....

After some testing (I should have done this earlier), it seems INDIRECT
can't resolve dynamic named ranges. I thought it could, but I was
wrong.

You're not going to be able to use INDIRECT to get the value of
London!MonthSales using your approach to defining it. I should have
asked befo why do you need a ROW() call in the definition of
MonthSales? If MonthSales were always in the same cell location on each
worksheet, you could define MonthSales at workbook-level as the cell's
address as text, e.g., as "x99". Then you could use

=INDIRECT(B$5&"!"&MonthSales)

to pull the MonthSales figure from the worksheet named in the calling
worksheet's B5 cell. If the cell location varies worksheet to
worksheet, you're always pulling it from column 2 of the named data
range, e.g., London!data, so only the row would vary from table to
table. That being the case, you could replace your INDIRECT formulas
with VLOOKUP cell formulas like

=VLOOKUP(INDIRECT(B$5&"!A"&2+ROW()+2),INDIRECT(B$5 &"!data"),2,0)

which assumes data is always defined as a literal range in each
worksheet. Note that the ROW() term would evaluate to the row in which
this formula were entered. This is no different than how your defined
name would evaluate if the INDIRECT call worked.