Results 1 to 3 of 3

Thread: [RESOLVED] Bizarre Windows 10 / Excel 2019 behavior

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Resolved [RESOLVED] Bizarre Windows 10 / Excel 2019 behavior

    I've used the attached workbook in Windows XP / Excel 2010 for 10 years without problems but when I switched to Windows 10 / Excel 2019 I get different results in column E. Column E should look like the picture and the values in column E like "22 / 115" are a ranking. The left side compares the current value in column D against all the values in column D and 15 indicates that it the 15th highest value. The right side (115) is the number of months since I started using the workbook.

    Here's a picture of what it looks like in Excel 2010. When the workbook is opened in Excel 2019 it also looks like that, but as soon as the last value in column D is changed, all the ranking change when normally only one or two might change.

    The rankings are done via code in the Worksheet_Change event. Can anyone correct it so that it works the way it used to? I wouldn't mind a formula approach.
    Name:  2021-09-26_16-24-49.jpg
Views: 137
Size:  82.6 KB
    Attached Files Attached Files

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Bizarre Windows 10 / Excel 2019 behavior

    Hi Martin,
    Excel 365 64-Bit here. Cannot confirm (as far as i can tell).

    But you asked for Formula-Approach.

    Look at attached workbook, column F.
    I've commented out your Worksheet-Change-Event

    The Font-Color-Change can be done with conditional formatting, but that's not my strong point

    EDIT: DateDif is an undocumented/secret Function. No intellisense etc.
    Attached Files Attached Files
    Last edited by Zvoni; Sep 27th, 2021 at 03:09 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

Posting Permissions

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



Click Here to Expand Forum to Full Width