Excel Data>Sort Issue

Rob_010101

Board Regular
Joined
Jul 24, 2017
Messages
198
Office Version
  1. 365
Platform
  1. Windows
Hello

Wondering if an Excel expert can help.

I'm wondering if this might be due to an update but i'm not entirely sure. I have a workbook at work which is used to track absence. There is a master data sheet which is refreshed with a system report every day, so on the absence sheet, all the employee's job titles / locations etc are kept up-to-date (via a vlookup from the master data sheet)

The absence sheet has about 5000 rows, the majority of these are filled with blanked formulas using =IF(OR(ISBLANK(N2)), "", VLOOKUP(@XXXXXX)) from column A to M. an employee ID is then added to column N and the employee's data will pop up in the columns before. This is excellent and does just the job we need it to.

Up until recently, however, a Data>Sort>Cell Values>A to Z would only sort the rows where the blanked formulas had returned values. So, I have about 500 rows of absence, where all the values are showing column A to M. Now, it wants to sort the blanked formulas with the 500 rows of returned values, meaning that whilst the data is sorted correctly, it lands up right at the bottom of the 5000 rows.

This happened suddenly out of the blue, so i'm not sure if it's a setting someone has changed, or an update to Excel that is causing this.

Kind Regards
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,223,879
Messages
6,175,148
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top