Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help - need to be able to sort alpabetically so logical values don't work
I have created a workbook in Excel 2002 that has 4 worksheets. 2 of
them interact with each other. One of the worksheets is for a client to enter detailed data into and the other automatically populates from some of the first worksheet. My problem is with the worksheet that basically just contains formulas in order to bring the data over from the other worksheet. I need to be able to do 2 things that I can't seem to do together. I need the data to be brought over and if the cell is blank I need it to return an empty cell, not a 0. I used a logical value formula to do that, but then because of the default sorting, when I try to sort the list alphabetically it puts all of the blank cells first (TRUE before FALSE). I need to find a way to populate the worksheet with data and still be able to sort alphabetically and not have a bunch 0's in the cells that brought over "blank" data. Any ideas? Does this post even make sense? It's hard to describe without being able to show it to you. Jessica |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help - need to be able to sort alpabetically so logical va
Since the letter "Z" is the last character in Excel's sort sequence, you
can't really have anything sorted after it. (Except empty cells, but if the cell contains a formula, it isn't empty, no matter what value it returns) A couple ideas come to mind that may help. One, is to return a value of "ZZZ" for blank cells. That way, they'd always be sorted last. If you really don't want the "ZZZ" to appear though, you could use Conditional Formatting to change the font color to match the backgroung (ie white on white) for any cell containing "ZZZ". Another option would be to use a helper column. Insert another column next to your data. In that column, use a formula like =IF(A1="",2,1). Then, when you sort, sort by the helper column first, then by your actual data column. This basically divides your data into two categories, blank and non-blank. All non-blank data (value of 1) will be sorted first, then all blank data (value 2) will be sorted last. HTH, Elkar " wrote: I have created a workbook in Excel 2002 that has 4 worksheets. 2 of them interact with each other. One of the worksheets is for a client to enter detailed data into and the other automatically populates from some of the first worksheet. My problem is with the worksheet that basically just contains formulas in order to bring the data over from the other worksheet. I need to be able to do 2 things that I can't seem to do together. I need the data to be brought over and if the cell is blank I need it to return an empty cell, not a 0. I used a logical value formula to do that, but then because of the default sorting, when I try to sort the list alphabetically it puts all of the blank cells first (TRUE before FALSE). I need to find a way to populate the worksheet with data and still be able to sort alphabetically and not have a bunch 0's in the cells that brought over "blank" data. Any ideas? Does this post even make sense? It's hard to describe without being able to show it to you. Jessica |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If, Logical Values. | Excel Discussion (Misc queries) | |||
Formula to sort text values with spaces | Excel Discussion (Misc queries) | |||
SUM for logical values | Excel Discussion (Misc queries) | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Worksheet Functions | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Discussion (Misc queries) |