Problem with date calculation after sorting

Status
Not open for further replies.

ely63

New member
Joined
Apr 9, 2014
Messages
6
Reaction score
0
Points
0
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:
View 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
View attachment 2767
Any suggestions would be appreciated. Thanks
 

Attachments

  • Date calculation test2.zip
    2 MB · Views: 2
Why have you reposted this question. It has been responded to already.
 
Status
Not open for further replies.
Back
Top