Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    The attached spreadsheet crashes Excel 2007: frequently, easily, and badly.

    My students isolated the problem. The spreadsheet contains a cell (B5) that has been named "inflationRate".

    When that cell is named "inflationRate" and that name is used in other spots in the spreadsheet, it will crash if you do just about anything, anywhere. If you change the formulas that use that named range to use the cell reference instead, the spreadsheet is stable.

    Does anyone have any idea what might cause this behavior?

    FWIW: the easiest way to turn this into a stable spreadsheet is to highlight B23.J23, and do a find and replace of "inflationRate" with "B5". Excel will make that change before crashing, and your autorecovered copy will retain the change and be stable. Note that this doesn't even get rid of the named range, it just stops using it.

    N.B. This behavior began after, and may have been caused by, converting this spreadsheet from xlsx to xls format.
    Attached Files Attached Files

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    How odd.
    I can confirm that the named cell causes the problem.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Boobounder,

    As a first shot I tried changing B5 to the following:

    =CONCATENATE("Inflation is ",TEXT(inflationRate,"##"),"%")

    No GO!

    Next I listed all your names see below:

    a_Hundred =100
    Athens =Sheet1!$B$16:$J$16
    Carthage =Sheet1!$B$15:$J$15
    chosenCity =Sheet1!$B$4
    ChosenVolume =Sheet1!$B$20:$J$20
    Cities =Sheet1!$A$15:$A$19
    cityCaption =Sheet1!$D$4
    CityVolumes =Sheet1!A$15:A$19
    inflationCaption =Sheet1!$D$5
    inflationRate =Sheet1!$B$5
    LastYear =Sheet1!IV:IV ---- There is nothing in this column
    priceCaption =Sheet1!$D$3
    PriceScenario =Sheet1!$B$23:$J$23
    PriceTable =Sheet1!$A$8:$B$10
    Revenues =Sheet1!$B$25:$J$25
    Rome =Sheet1!$B$19:$J$19
    scenario =Sheet1!$B$3
    Scenarios =Sheet1!$A$8:$A$10
    Sparta =Sheet1!$B$17:$J$17
    startYear =Sheet1!$B$12
    Troy =Sheet1!$B$18:$J$18
    Year =Sheet1!$B$12:$J$12

    LastYear is used in this formula:

    B23:J23 =IF(Year=startYear,INDEX(PriceTable,scenario,2),La stYear*(1+inflationRate/a_Hundred))

    If I use the F5 (GoTo) key to find: LastYear it highlights a different Column (very strange) --- Still no data!
    Note: Re doing the Paste List of names still shows LastYear as above?

    Seems like something has become seriously corrupted in this file.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RetiredGeek:

    There is no problem with range name LastYear.
    Lastyear contains a RELATIVE reference and hence issuing Goto will have different results if your starting cell is different, the refers to formula of LastYear will be relative to the current cell. Try this:
    Select cell A1. Open the name manager. LastYear refers to IV:IV.
    Select cell F1. Open Name Manager.LastYear refers to E:E
    So LastYear always refers to the entire column to the immediate left of the cell Lastyear is used in.
    Because the formula LastYear is used in is NOT an array formula, Lastyear in fact gets the data form the cell to its immediate left.

    boobounder:
    I can confirm the crash too, very odd. Looks like the sheet acquired a corruption or you have encountered an obscure bug in Excel!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The file definitely has a corruption somehow. If I rebuild the file using a tool I have for that purpose, the sheet works without a problem.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by pieterse View Post
    Lastyear contains a RELATIVE reference and hence issuing Goto will have different results if your starting cell is different,
    Duh! Sorry I missed this.

    I've been playing with the file back and forth with Win XP-Excel 2003 (totally stable) and
    Win 7-Excel 2007 (totally flaky). It's got me totally stumped.

    Have you tried recreating the file from scratch and doing an initial save as .xls?
    The file may have been corrupted when you did the conversion to .xls from .xlsx.
    However, it is really strange that this only affects the way it works in 2007 and not 2003.
    Go figure.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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