Sorting Blank Values

[The Wolf]

I have a worksheet with 5000 rows of formulas. The formula in column A is:

=IF(Sheet1!A9="","",Sheet1!A9)

The value in Sheet1!A9 is either a number or there is no value in the cell. The problem occurs when I sort on this column. If I sort descending, all of the blank values are at the top of the list, then followed by numbers. I always want the blanks to be at the bottom, but I still need the ability to sort either ascending or descending.

 

[Andrew Poulsom]

Blank cells always come last but the trouble is "" is not the same as blank.

You may be able to create a dynamically sorted list using the LARGE function. What range of data are you referencing and where are you putting the results you want sorted?

 

[The Wolf]

On 2002-09-16 08:53, Andrew Poulsom wrote:
Blank cells always come last but the trouble is "" is not the same as blank.

You may be able to create a dynamically sorted list using the LARGE function. What range of data are you referencing and where are you putting the results you want sorted?

In sheet 1, the data range is variable (the number for rows could be 100, or they could be 1000, as it changes daily). Therefore, to compensate for the variability, I entered the formula I referenced earlier from cell a2 to a3000 in sheet 2. I need to be able to sort on this column in sheet 2. There are other columns of data in sheet 2 as well, which I will also use to sort this data.

 

[Andrew Poulsom]

Enter this formula in cell A2 on Sheet2:

=IF(ISERROR(LARGE(Sheet1!$A:$A,ROW(A1))),"",LARGE(Sheet1!$A:$A,ROW(A1)))

and copy down. It assumes column A on contains numbers or "".

No more sorting!