Results 1 to 5 of 5

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:
    Click image for larger version. 

Name:	sort options.jpg 
Views:	15 
Size:	59.3 KB 
ID:	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
    Click image for larger version. 

Name:	version.JPG 
Views:	10 
Size:	14.2 KB 
ID:	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,437
    Articles
    0
    Excel Version
    Excel 2016
    When you are referencing the sheet that you are actually in, it is best not to include the sheetname. Adding the sheetname tends to cause this issue.

    Try, instead:

    ='EXCLUSIONS AND VARIABLES'!$F$1-D2

    copied down


  3. #3
    Thanks NBVC

    that seems to work. The referencing was done by Excel automatically as I selected the cell I wanted (rather than manually inputting it). :-)

    Incidentally, why does the problem disappears when there are less rows?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by ely63 View Post
    Thanks NBVC



    Incidentally, why does the problem disappears when there are less rows?
    Less Rows? Not sure I understand. It is not less rows, it's the referencing.


  5. #5
    The spreadsheet has 11000+ rows. When I tried to reduce it down to 5000 rows to make it more uploadable, the problem disappeared, and I could not replicate it.

    Believe me, I have tried all morning all sorts of combinations of sorting, adding, removing etc. And all along was stupid excel referencing a sheet it was already in!!

    Just curiosity anyway, the solution you gave earlier was spot on!

    Thanks :-)

Posting Permissions

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