referencing named formula using INDIRECT function

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("filename"))-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

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


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



--
Regards,

Peo Sjoblom


<rob.hick@nbs.nhs.uk> wrote in message
news:1115129310.053429.314110@f14g2000cwb.googlegroups.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("filename"))-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
>

thanks for the tip Peo, but it doesn't seem to have worked. All the
names of the worksheets are single words (no spaces) so it shouldn't
matter whether I have quotes or not (if I've understood you correctly)

=INDIRECT(cellabove&"!A1")

worked for me.

--

Vasant

"Rob Hick" <rob.hick@nbs.nhs.uk> wrote in message
news:1115130295.855375.177530@z14g2000cwz.googlegroups.com...
> thanks for the tip Peo, but it doesn't seem to have worked. All the
> names of the worksheets are single words (no spaces) so it shouldn't
> matter whether I have quotes or not (if I've understood you correctly)
>

that not quite what i was after Vasant - i need to reference the name
rather than 'A1'. So on my workbook (XL2000):

=London!MonthSales
is ok but
=INDIRECT("London!MonthSales")
or any permuation of getting the correct text, does not work

Rob

How is the name defined? Go to insert>name>define and select MonthSales, now if
it for sinstance says something like =London!$C$2

then you can use

=INDIRECT("MonthSales")

This also works for me

=INDIRECT("London!MonthSales")

assuming that London is the sheet name and not the workbook name

Regards,

Peo Sjoblom



"Rob Hick" wrote:

> that not quite what i was after Vasant - i need to reference the name
> rather than 'A1'. So on my workbook (XL2000):
>
> =London!MonthSales
> is ok but
> =INDIRECT("London!MonthSales")
> or any permuation of getting the correct text, does not work
>
> Rob
>
>

Again, under the scenario you described:

=INDIRECT(cellabove&"!MonthSales")

works just fine for me.

"cellabove" is a named formula that returns the contents of the cell above
it. The cell above it contains the text "London". The sheet named London has
a named range called MonthSales. The above formula returns the contents of
this range.

I don't see the problem.

--

Vasant


"Rob Hick" <rob.hick@nbs.nhs.uk> wrote in message
news:1115131576.455545.142360@g14g2000cwa.googlegroups.com...
> that not quite what i was after Vasant - i need to reference the name
> rather than 'A1'. So on my workbook (XL2000):
>
> =London!MonthSales
> is ok but
> =INDIRECT("London!MonthSales")
> or any permuation of getting the correct text, does not work
>
> Rob
>

rob.h...@nbs.nhs.uk wrote...
....
>The MonthSales named formula is defined as follows:
>
>WorksheetName=RIGHT(CELL("filename"),
>LEN(CELL("filename"))-FIND("]",CELL("filename")))

Do you mean WorksheetName is defined as above?

What does the cell formula

="["&WorksheetName&"]"

return?

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

Why 2+ROW()+2 rather than ROW()+4?