Data set sorting by date misses some rows in the sort

[hstaubyn]

I've been using Excel for 20 years and have only come across this problem when the formula in the sort column contains the worksheet name. It's a mystery...

I have a largeish data set and perform a custom sort on 3 columns.

Level 1: Text A->Z
Level 2: Text A->Z
Level 3: Date Oldest->Newest

The cells in the date column do contain a drag copied formula, but the worksheet name of the sheet with the data set I'm sorting is not in it. Another sheet name is in the formula, however, which is of the form IFERROR ( OFFSET ( MATCH ( ) ) , OFFSET ( MATCH ( ) ) ). The OFFSET contains a reference to a specific cell in another worksheet and the MATCH contains a reference to a range in that same worksheet.

When I sort, it does it correctly all except for the date level. It kind of sorts on date, with most rows correctly grouped together at least and those groups increasing in time as you go down, but it's basically a mess. The same thing happens when I sort on that column only using the autofilter.

Playing around, I copied the values and formatting (i.e. without any formulae) onto a fresh worksheet, tried the same sort there, and it worked perfectly. So that would indicate that it's something to do with the fact that the date column contains formulae.

Does anyone know what might be causing this?

 

[hstaubyn]

Update on the above issue.

I noticed that when I hit OK on the custom sort, it instantly sorts everything correctly. Then I get the waiting circle for about 10-15 seconds, and at the last moment before it stops, the rogue rows flip back into their incorrect places.

It looks like the actions of a buggy macro, but I'm not using VBA in this workbook.

 

[awoohaw]

can you share minisamples of your worksheets? Or post the workbook on a file sharing site?