referencing named formula using INDIRECT function

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
>
>

to clarify - the name MonthSales is a local name for the worksheet
'London'. It is defined above in my first post. I am referencing it
in another worksheet called 'Summary'.

could the problem be because the name effectively returns a value and
not a reference?

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
>

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).

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.

Vasant - have you defined the names exactly as I have put above? In
which case, is this a problem in XL2000?

Thanks for all your help,
Rob

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.

thanks for all the pointers Harlan. I'd misunderstodd what the
CELL("filename") function returned so I'm not sure that my
'WorksheetName' name is actually going to always return what i require.
Is there any other funtion that will achieve what I'm after?

As regards the --W=ADorksheetName&"!$A"&2+ROW()+X -- part, the X is just
a name (Row_Offset =3D 2) so it shouldn't be causing any errors. And the
2+ROW()+X should return a number everytime but I tried your sugestion
of TEXT() just in case nad it didn't solve the problem.

The problem with trying to isolate the expression where the error
occurs is that the error doesn't occur if i reference the name
directly, i.e. =3DLondon!MonthSales; the problem only comes if i
reference it using the INDIRECT function to 'build' the reference text
from data in other cells.

Thanks again
Rob