+ Reply to Thread
Results 1 to 6 of 6

Index formulas suddenly don't work

  1. #1
    Registered User
    Join Date
    01-25-2010
    Location
    Joinville, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    42

    Index formulas suddenly don't work

    Hello!

    After almost a month where i have not touched my large excel sheet, suddenly all the index formulas that worked several times previously don't work. This formula was made with the help of NBVC on the following thread:

    http://www.excelforum.com/excel-gene...opy-paste.html

    The formulas look something like this:

    Please Login or Register  to view this content.

    Portuguese translation for functions:
    INDEX = ÍNDICE
    MATCH = CORRESP
    TEXT = TEXTO
    YEAR = ANO

    Basically, if I grab the sheet that used to work, go into a cell that contains this formula and hit enter, the result suddenly changes to #N/D. Does anybody have any ideas how this could have happened?

    Thanks in advance.
    Last edited by FortuneSyn; 05-03-2010 at 04:51 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Index formulas suddenly don't work

    Suspects:

    1) The sheetname no longer matches accurately
    2) The data being analyzed is no longer accurate. That could be the ANO() or the CORRESP() formula, or both.

    Highlight a misbehaving cell and press use the Evaluate Formula tool in the Formula Auditing toolbar to step through the formula one calc at a time, you'll see when it breaks.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-25-2010
    Location
    Joinville, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Index formulas suddenly don't work

    =ÍNDICE(a!$B$7:$XFD$1048576;CORRESP($A446;a!$A$7:$A$1048576;0);CORRESP(TEXTO(ANO(C$445);"0000");a!$B$4:$XFD$4;0)+CORRESP(TEXTO(C$445;"mm-mmmm");a!$B$5:$XFD$5;0)-1)

    The part of the formula that gives error is:

    +CORRESP(TEXTO(C$445;"mm-mmmm");a!$B$5:$XFD$5;0)-1)

    The result of
    TEXTO(C$445;"mm-mmmm")

    is
    "02-februar"

    whereas the text it's supposed to match is
    "02-fevereiro"

    the result is in danish, the text it's suppose to match is in portuguese. I am guessing this must be the error.

    So the question is, how can I get the result to be changed from danish to portuguese? (I bought this laptop in denmark, hence the danish)

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Index formulas suddenly don't work

    you could change the regional settings in control panel to match the language, but you'd have to renter the formula so it takes effect eg if it text(a1,"mmmm") said October and you then change the regional settings to Portuguese the date would be in Portuguese but
    text(a1,"mmmm") would still say October until you either double click it or re-enter it then it would return Outubro
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Forum Contributor
    Join Date
    02-25-2010
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    174

    Re: Index formulas suddenly don't work

    In 2007 you can click the Office Button (in the upper left) and then 'Excel Options' (bottom right) and under the 'Popular' tab go to Language Settings.

    This may change the default month names used.

  6. #6
    Registered User
    Join Date
    01-25-2010
    Location
    Joinville, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Index formulas suddenly don't work

    Thankyou!!! The issue has been solved. I solved it by changing the regional settings from Denmark to Brazil, and suddenly all my graphs showed again!

    First I tried doing what DP978 mentioned, but it didn't help (maybe i had to shut down entire laptop I don't know).

    Anyways, will mark this as solved.

+ 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