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
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