+ Reply to Thread
Results 1 to 20 of 20

referencing named formula using INDIRECT function

Hybrid View

Guest referencing named formula... 05-03-2005, 11:06 AM
Guest Re: referencing named formula... 05-03-2005, 11:06 AM
Guest Re: referencing named formula... 05-03-2005, 11:06 AM
Guest Re: referencing named formula... 05-03-2005, 11:06 AM
Guest Re: referencing named formula... 05-03-2005, 11:06 AM
Guest Re: referencing named formula... 05-03-2005, 12:06 PM
Guest Re: referencing named formula... 05-03-2005, 12:06 PM
Guest Re: referencing named formula... 05-03-2005, 01:06 PM
Guest Re: referencing named formula... 05-04-2005, 05:06 AM
Guest Re: referencing named formula... 05-03-2005, 01:06 PM
  1. #1
    Peo Sjoblom
    Guest

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



  2. #2
    Rob Hick
    Guest

    Re: 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)


  3. #3
    Vasant Nanavati
    Guest

    Re: referencing named formula using INDIRECT function

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




  4. #4
    Rob Hick
    Guest

    Re: referencing named formula using INDIRECT function

    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


  5. #5
    Peo Sjoblom
    Guest

    Re: referencing named formula using INDIRECT function

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


  6. #6
    Rob Hick
    Guest

    Re: referencing named formula using INDIRECT function

    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?


  7. #7
    Vasant Nanavati
    Guest

    Re: referencing named formula using INDIRECT function

    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
    >




  8. #8
    Rob Hick
    Guest

    Re: referencing named formula using INDIRECT function

    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


  9. #9
    Harlan Grove
    Guest

    Re: referencing named formula using INDIRECT function

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1