Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofilter SORT that Ignores Forced Blanks ("")
Is there a way to get the Autofilter Sort function to ignore columns that have a forced blank ("") result in them? I'm trying to sort a column with a formula in each cell that results in either a % or a blank result (""). When I sort descending, Autofilter puts the blank results at the top of the list. The ideal solution would be something that let me do this... - if(isblank($A1),INSERTBLANK(),B1*C1) vs. - if(isblank($A1),"",B1*C1) Any help would be greatly appreciatd! -- bryhogan ------------------------------------------------------------------------ bryhogan's Profile: http://www.excelforum.com/member.php...fo&userid=8879 View this thread: http://www.excelforum.com/showthread...hreadid=498680 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofilter SORT that Ignores Forced Blanks ("")
Anybody? I'm a little desperate! -- bryhogan ------------------------------------------------------------------------ bryhogan's Profile: http://www.excelforum.com/member.php...fo&userid=8879 View this thread: http://www.excelforum.com/showthread...hreadid=498680 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofilter SORT that Ignores Forced Blanks ("")
Instead of putting a blank in the cell, you should put a very small
number in there, one which is smaller than the smallest B1*C1 value - as this is a %age then I would have thought that .000000001 would be small enough! When you sort it in descending order on this column these very small values will appear at the bottom - you can easily highlight them all and press <delete to turn them into proper blanks for future work on the sheet. Hope this helps - I didn't see your earlier post as I was out. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofilter SORT that Ignores Forced Blanks ("")
Thanks, Pete. Unfortunately, I need the flexibility of sorting ascending or descending and not including those items. If I could find a way to get SORT to ignore those cells (as if they were truly blank), then that would work. -- bryhogan ------------------------------------------------------------------------ bryhogan's Profile: http://www.excelforum.com/member.php...fo&userid=8879 View this thread: http://www.excelforum.com/showthread...hreadid=498680 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofilter SORT that Ignores Forced Blanks ("")
Maybe insert a new column that treats those "" cells the way you want.
=if(a2="",rept("z",255),a2) (or something that sorts to the bottom) Then sort on that column. (Hide/delete it when you're done.) bryhogan wrote: Thanks, Pete. Unfortunately, I need the flexibility of sorting ascending or descending and not including those items. If I could find a way to get SORT to ignore those cells (as if they were truly blank), then that would work. -- bryhogan ------------------------------------------------------------------------ bryhogan's Profile: http://www.excelforum.com/member.php...fo&userid=8879 View this thread: http://www.excelforum.com/showthread...hreadid=498680 -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofilter SORT that Ignores Forced Blanks ("")
Thanks, Dave. I'll try that... -- bryhogan ------------------------------------------------------------------------ bryhogan's Profile: http://www.excelforum.com/member.php...fo&userid=8879 View this thread: http://www.excelforum.com/showthread...hreadid=498680 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofilter SORT that Ignores Forced Blanks ("")
Dave,
I was reading through trying to see if my answer would be in here, and it wasn't and of course, for some reason I can't post a new question. My question does relate to sorting however - I have columns that I need to sort ascending - but I do not want to include any of the titles that I have put on the page, just the information under their titles (for Example : one title is Region. I don't highlight that, just the cells underneath, and when I hit the sort key, it automatically sorts the title of the worksheet, as well as the title of the cell.This makes some of the information above the title, and the rest below!) I hope this makes sense. I am getting a little desperate. I have a project due in a few weeks and it has to be perfect and I need to go over 24 months worth of data!!! :) please help me! "Dave Peterson" wrote: Maybe insert a new column that treats those "" cells the way you want. =if(a2="",rept("z",255),a2) (or something that sorts to the bottom) Then sort on that column. (Hide/delete it when you're done.) bryhogan wrote: Thanks, Pete. Unfortunately, I need the flexibility of sorting ascending or descending and not including those items. If I could find a way to get SORT to ignore those cells (as if they were truly blank), then that would work. -- bryhogan ------------------------------------------------------------------------ bryhogan's Profile: http://www.excelforum.com/member.php...fo&userid=8879 View this thread: http://www.excelforum.com/showthread...hreadid=498680 -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofilter SORT that Ignores Forced Blanks ("")
Kendra,
I have responded to your other post. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort, Copy, Paste..without Autofilter..Anyone??? | Excel Worksheet Functions | |||
How do I sort a list that contains blanks that I want to keep? | Excel Discussion (Misc queries) | |||
Can I remove blanks from a range without using sort? | Excel Discussion (Misc queries) | |||
how do I reset the default sort order in excel xp back to blanks . | Excel Discussion (Misc queries) | |||
excel needs feature to delete blanks when autofilter is used. | Excel Discussion (Misc queries) |