Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    INDIRECT function (WinXP/O2003)

    Hi,

    Have a sheet in which I load in currencies as an updateable XML data query from a website (works without any problems)
    Cells in this sheet are named according to ISO convention eg. USD, GBP etc.
    On other sheet I would like to be able to in eg. column B to type in an ISO. Column C an amount and in Column D to convert it into my base currency.

    Intention is that input only goes to B and C and that D then is calculated and simply can be copy pasted downwards - irrespective of the currency.
    Formula used: C2*INDIRECT (B2)

    If the named cells are on same sheet as the formula it works fine. If they are moved to a seperate sheet they return #VALUE#

    Any idea why they will only work as long as they are on the same sheet?
    - is this a limitation of some kind to the indirect function ?


    Any hints are appreciated.
    Thanks
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INDIRECT function (WinXP/O2003)

    Perhaps the names are local to the worksheet. You can use <!profile=Pieterse>Pieterse<!/profile>'s free Name Manager add-in to convert the names to global names. See JKP-ADS Download page.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INDIRECT function (WinXP/O2003)

    Hi Hans,

    Thanks for the tip, took a look at the name manager, but I'm not 100% certain how to make distinction between "sheet-local" and "global" names - and how they work. Can you elaborate slightly... - please - ;o)
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INDIRECT function (WinXP/O2003)

    See MSKB article HOW TO: Use Global and Local References in Formulas in Excel 2000. It applies to other versions of Excel as well.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INDIRECT function (WinXP/O2003)

    OK, my perceptions were in line with the MSKB article.

    Tried to create an example for you and then everything worked as supposed to.
    I think reason could be that I copied global identical names into an existing workbook and that this in some way caused a conflict.
    Had for instance currencies USD and EUR in both as globals.

    anyway, problem seems to be solved - thanks for the enlightning articles.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

Posting Permissions

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