You need to use quotes for the named cell, i.e. assume the cell above is C2


=INDIRECT("'"&C2&"'!MonthSales")



--
Regards,

Peo Sjoblom


wrote in message
oups.com...
Hi,

I'm having some problems using the INDIRECT function to reference a
named formula on a different sheet. I want to be able to put the name
of the sheet in one row (as a table header) and the use the INDIRECT
function to reference a particular named formula that is on that sheet.
As an example, on sheet 'Summary' i want to put 'London' in one cell
and then below that put =INDIRECT(cellabove&"!MonthSales) rather than
putting =London!MonthSales. I want to do this partly so i don't have
to copy new formulas for new sheets and also for data security - so i
don't accidently reference the wrong sheet.

However, the INDIRECT() formula returns #ref!

I think the problem is with the definition of the named formula
(MonthSales) on the 'London' sheet because it requires the return of a
variable using the CELL('filename') function, and the ROW(). I'm
guessing that when you use the INDIRECT function, the formula no longer
'knows' which cell it is in??

The MonthSales named formula is defined as follows:

WorksheetName=RIGHT(CELL("filename"),LEN(CELL("fil ename"))-FIND("]",CELL("filename")))

MonthSales==VLOOKUP(INDIRECT(WorksheetName&"!$A"&2 +ROW()+2),London!data,2,FALSE)

The formula, although a little complicated, does work if i reference it
using the London!MonthSales syntax but not the INDIRECT() syntax. Can
anyone shed some light on this and say whether there is a
workaround/point out what i'm doing wrong.

Thanks
Rob