I have a spreadsheet of 31 columns. Rows 1-16 have data in them, but I do not want to sort them. They are calculating amounts, percentages, etc using a variety of functions (count, sum, etc).
I want to sort the data starting from row 18 down using data filter, controlled by one column that should be returning numeric values. Here is the formula in that column:
=IF(L18="","",IF(R18+Q18-P18=0,0,R18+Q18-P18))
The data in column L is text, if that means anything. The data in R, Q, and P are all numbers, which are calculated from other numbers pasted into columns C thru J.
I have selected row 17, which contains all my headers, and added a data filter. When I select the filter button on cell S17, it wants to sort the column as if text (sort A to Z, sort Z to A), although the results there are mainly numbers. I believe it is inserting the "" results as blanks.
The irritating thing is this was sorting as numbers 2 days ago. This is a template I am constantly editing daily, so I cannot just go back to the one that was working because that would be 2 days worth of edits lost. I added columns 31 and 32 and when I un-filtered and re-filtered row 17 to include these additions, that's when this problem arose.
I have searched on the internet and found many forums attempting to solve different versions of this issue, but none of them match what I need to do. I want to sort largest to smallest anything resulting in negative numbers, positive numbers, or zeros, which should be anything that has data in column L, and leave anything else out of the filter/sort. I cannot just remove blanks, as this is a template used by my entire department and the number of rows of data they paste into columns A to L can range in number of rows from 1 to 100s. I want to avoid having to use VBA or a macro. I know it is possible to avoid that, as this was just working the other day. I just don't know what I did to break it.
HELP!!!
Bookmarks