|  | Sort Descending with IF and empty cells | | |
| | | Mike |  |
|  Posted: Tue Jun 10, 2008 6:57 pm Post subject: Sort Descending with IF and empty cells |  |
I am trying to sort a column descending. The cells functions are
=IF(J38=0,"",I38/J38). I want nothing to appear in the cell if
the result is true, but this poses a problem when I run my macro for sorting
descending. It puts my blank cells at the top. Is there a way to sort
descending but have my blank cells all at the bottom? Any assistance would
be appreciated.
--
Mike |
| |
| | | Pete_UK | |
| | Posted: Wed Jun 11, 2008 1:17 am Post subject: Re: Sort Descending with IF and empty cells | |
Instead of returning "", you could return a large negative value, like
-9999. You can apply conditional formatting such that if the cell
contains -9999 the foreground colour is set to white, so that it
appears blank. When you sort in descending order these records will
drop to the bottom.
If you are using a macro, though, you could physically empty the cells
so that they do not contain "", so then a sort would put them at the
bottom anyway.
Hope this helps.
Pete
On Jun 10, 9:57 pm, Mike <M...@discussions.microsoft.com> wrote:
| Quote: | I am trying to sort a column descending. The cells functions are
=IF(J38=0,"",I38/J38). I want nothing to appear in the cell if
the result is true, but this poses a problem when I run my macro for sorting
descending. It puts my blank cells at the top. Is there a way to sort
descending but have my blank cells all at the bottom? Any assistance would
be appreciated.
--
Mike |
|
| |
| | | Mike | |
| | Posted: Wed Jun 11, 2008 4:25 pm Post subject: Re: Sort Descending with IF and empty cells | |
| |  | |
Perfect. Thank you.
--
Mike
"Pete_UK" wrote:
| Quote: | Instead of returning "", you could return a large negative value, like
-9999. You can apply conditional formatting such that if the cell
contains -9999 the foreground colour is set to white, so that it
appears blank. When you sort in descending order these records will
drop to the bottom.
If you are using a macro, though, you could physically empty the cells
so that they do not contain "", so then a sort would put them at the
bottom anyway.
Hope this helps.
Pete
On Jun 10, 9:57 pm, Mike <M...@discussions.microsoft.com> wrote:
I am trying to sort a column descending. The cells functions are
=IF(J38=0,"",I38/J38). I want nothing to appear in the cell if
the result is true, but this poses a problem when I run my macro for sorting
descending. It puts my blank cells at the top. Is there a way to sort
descending but have my blank cells all at the bottom? Any assistance would
be appreciated.
--
Mike
|
|
| |
| | | Pete_UK | |
| | Posted: Thu Jun 12, 2008 10:59 pm Post subject: Re: Sort Descending with IF and empty cells | |
| | | |
Glad to hear it, Mike - thanks for feeding back (have been away for a
few days).
Pete
On Jun 11, 7:25 pm, Mike <M...@discussions.microsoft.com> wrote:
| Quote: | Perfect. Thank you.
--
Mike
"Pete_UK" wrote:
Instead of returning "", you could return a large negative value, like
-9999. You can apply conditional formatting such that if the cell
contains -9999 the foreground colour is set to white, so that it
appears blank. When you sort in descending order these records will
drop to the bottom.
If you are using a macro, though, you could physically empty the cells
so that they do not contain "", so then a sort would put them at the
bottom anyway.
Hope this helps.
Pete
On Jun 10, 9:57 pm, Mike <M...@discussions.microsoft.com> wrote:
I am trying to sort a column descending. The cells functions are
=IF(J38=0,"",I38/J38). I want nothing to appear in the cell if
the result is true, but this poses a problem when I run my macro for sorting
descending. It puts my blank cells at the top. Is there a way to sort
descending but have my blank cells all at the bottom? Any assistance would
be appreciated.
--
Mike- Hide quoted text -
- Show quoted text - |
|
| |
|
|
