referencing named formula using INDIRECT function

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

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

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?

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?

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

Rob Hick wrote...
>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?
..=2E.

If you're using CELL("Filename") in a defined name, and you want it to
return the workbook/worksheet name of the calling cell, then change it
to

CELL("Filename",INDIRECT("A1"))

When that defined name appears in a cell formula, Excel will evaluate
it in the context of that cell, in which case INDIRECT("A1") will
resolve to the A1 cell in the same worksheet as the calling cell.

> . . . but I tried your sugestion
>of TEXT() just in case nad it didn't solve the problem.

That was a shot in the dark in case you had Transition Formula
Evaluation enabled. Unlikely, but a possibility.

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

Your original post showed that you were defining London!MonthSales as

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

Are you saying that if you enter that formula exactly in a cell that
you get the result you want, but if you enter

=3DLondon!MonthSales

in the cell you don't? Or do you mean that if, say, cell X99 contained

London!MonthSales

that the formula

=3DINDIRECT(X99) [or =3DINDIRECT("London!MonthSales")]

returns a #REF! error? If the latter, London!MonthSales refers to a
formula that returns a value rather than a range reference. INDIRECT
can only return range references, so it can't return the value given by
London!MonthSales. You could fix this by defining London!MonthSales as

=3DINDEX(=ADLondon!data,MATCH(INDIRECT(W=ADorksheetName&"!$A"&2+ROW()+2),
INDEX(=ADLondon!data,0,1),0),2)

INDEX, in this case, returns a range reference rather than a value, so
INDIRECT("London!MonthSales") would be able to return a range
reference, which would be resolved to its value.

great stuff.

the CELL("Filename",INDIRECT("A1")) works perfectly to return the
reference of the calling cell.

the latter assumption is correct - so defining the name as above, then
in a cell putting =London!MonthSales works but
=INDIRECT("London!MonthSales") does not.

I tried reverting to the INDEX style syntax (great tip - thanks) but
unfortunately this doesn't work either - still getting a #REF! error.
What is even more strange is that putting
=CELL("address",London!MonthSales) returns the correct cell reference
as you say....?

There must be something in the definition of the names that can't be
resolved when the reference uses INDIRECT.

I tried putting =INDIRECT("WorksheetName") in a cell and it returns the
#REF! error - could this be the problem?

NB. WorksheetName =RIGHT(CELL("filename",INDIRECT("A1")),
LEN(CELL("filename",INDIRECT("A1")))-
FIND("]",CELL("filename",INDIRECT("A1"))))

thanks
Rob

Rob Hick wrote...
..=2E.
>I tried reverting to the INDEX style syntax (great tip - thanks) but
>unfortunately this doesn't work either - still getting a #REF! error.
>What is even more strange is that putting
>=3DCELL("address",London!MonthSales) returns the correct cell reference
>as you say....?
..=2E.

There's a ROW() call inside your INDIRECT call. That could be a
problem. Very esoteric, but I should have caught it before. Anyway, try
defining London!MonthSales as

=3DINDEX(=ADLondon!data,MATCH(INDI=ADRECT(W=ADorksheetName&"!$A"&2+SUM(RO=
=ADW())+2),

INDEX(=ADLondon!data,0,1),0),2)

That is, replace the ROW() call with SUM(ROW()). While this seems odd
and unnecessary, ROW() always returns arrays, and this converts the
single entry array to a scalar/nonarray single number.


>I tried putting =3DINDIRECT("WorksheetName") in a cell and it returns
the
>#REF! error - could this be the problem?
..=2E.

This is correct and expected. Worksheet returns a text string, so
INDIRECT couldn't return a range reference.

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

i've tried to boil the problem down to see what's happening and i have
a feeling i might be misunderstanding the INDIRECT function. As i
understand it the following should work:

A B
1 cat INDEX(A1:A2,2,0)
2 dog =INDIRECT(B1)

so that in cell B2, the value 'dog' is returned but it seems to return
#REF!

=INDIRECT(B1)

will not work unless B1 contains a cell reference. Since B1 contains a
formula, you have to use the cell address (a string):

=INDIRECT("B1")

--

Vasant

"Rob Hick" <rob.hick@nbs.nhs.uk> wrote in message
news:1115734148.946239.204750@f14g2000cwb.googlegroups.com...
> ok. tried putting the SUM(ROW()) in but still didn't fix the problem -
> using INDIRECT(...) still returns #REF!
>
> i've tried to boil the problem down to see what's happening and i have
> a feeling i might be misunderstanding the INDIRECT function. As i
> understand it the following should work:
>
> A B
> 1 cat INDEX(A1:A2,2,0)
> 2 dog =INDIRECT(B1)
>
> so that in cell B2, the value 'dog' is returned but it seems to return
> #REF!
>

ahhh, that could possibly explain the problem then...

in my example above, if you make the change as Vasant suggests then in
cell B2 you simply get the text 'INDEX....' rather than the result of
evaluating the formula. if you make B1 a formula (put '=' infront)
then B2 returns the same value as B1 (dog), as you might expect.

if you now change the table above to include the ROW() function you
get:

A B
1 cat =INDEX(A1:A2,SUM(ROW())+1,0)
2 dog =INDIRECT("B1")

then B2 returns the same value again as B1 (cat) which again is
probably expected.

In my problem, the named formula contains a similarish function to that
in B1 above but unless I directly refer to the formula in a cell, the
ROW() part of the function cannot be evaluated. Thus when i try to use
the INDIRECT function to reference the name (rather than the name
evaluated in a cell) it returns #REF!

i was previously assuming (bad i know!) that the INDIRECT function
would evaluate the named formula in the context of the cell it was in
but this appears to be wrong.

does this make sense and does it explain the results i'm getting?

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 before: 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.

thanks for the help and suggestions Harlan. Your final assumption is
correct - that the cell location differs. It's a bit involved to
explain exactly what i need this for without you seeing the
spreadsheet; to be honest i'm probably making it over complicated in
pursuit of unnecessary flexibility but once you start down the route,
the challenge grips you!

however, i may well replace the named range with a formula as you
suggest - it seems the logical thing to do.

thanks again for all your help, and to everyone else who contributed.

Rob