Results 1 to 2 of 2

Thread: Problem with date calculation after sorting

  1. #1

    Problem with date calculation after sorting



    Register for a FREE account, and/
    or Log in to avoid these ads!

    APOLOGIES ABOUT THE LARGE SPREADSHEET ATTACHED, BUT THERE IS A REASON FOR IT. (if it's not attached or other problems let me know I can email it, I had trouble uploading the xls file originally due to filesize)

    The issue is with the date formula in col I (age of tariff). It subtracts the relevant tariff date in Col D from the fixed date in sheet EXCLUSIONS AND VARIABLES, F1.
    The formula looks like this: ='EXCLUSIONS AND VARIABLES'!$F$1-Psversion_Detail!D2
    When you copy it down it becomes, as it shoud:
    ='EXCLUSIONS AND VARIABLES'!$F$1-Psversion_Detail!D2
    ='EXCLUSIONS AND VARIABLES'!$F$1-Psversion_Detail!D3
    ='EXCLUSIONS AND VARIABLES'!$F$1-Psversion_Detail!D4
    Etc Ė so far so good.
    I now apply the following sort options:
    Attachment 2766
    If you look at the formulas now, they appear as:
    Row2:='EXCLUSIONS AND VARIABLES'!$F$1-Psversion_Detail!D2
    Row3:='EXCLUSIONS AND VARIABLES'!$F$1-Psversion_Detail!D9312
    Row4:='EXCLUSIONS AND VARIABLES'!$F$1-Psversion_Detail!D3
    Row5:='EXCLUSIONS AND VARIABLES'!$F$1-Psversion_Detail!D9313
    Row6:='EXCLUSIONS AND VARIABLES'!$F$1-Psversion_Detail!D4
    Row7:='EXCLUSIONS AND VARIABLES'!$F$1-Psversion_Detail!D9314
    etc
    For the life of me I canít understand what I have done wrong, and I am starting to think itís a bug. I'd like to be proven wrong so I can get on with it and fix it! Incidentally none of the other formulas seem to be affected (for example the ones in col H or col J). Could someone with the same version of excel try it on their machines, and maybe someone with a different version?
    Interestingly, when I started experimenting, and cut down the number of rows to 5000, the problem seems to disappear. Does anyone know of any reported cases? This is the version of excel I use
    Attachment 2767
    Any suggestions would be appreciated. Thanks
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Why have you reposted this question. It has been responded to already.


Posting Permissions

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