Problem with date calculation after sorting

[ely63]

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:

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

Any suggestions would be appreciated. Thanks

 

[NBVC]

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

 

[ely63]

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). :bounce:

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

 

[NBVC]

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.

 

[ely63]

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