• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with sorting blank cells got as results from formulas

D

davecrt

Member
Guys, hard problem, need help

I must report all the cells from a previous sheet that respect a particular condition, otherwise consider the cell like blank. the problem is that this must be automatic, in the sense that i need that this operation devolps every time with different records sheet. the problem is that excel, in the second sheet, consider the blank cells which are the result of the conditional formulas non like blank cells, and in fact when i try to order the numbers obtained like result, excel orders the blank cells before the numeric cells. I can not delete the formulas in the blank cells because it must be able to operate with different sheet record time after time (lenght can vary).

question: How i can get blank cells like result from a formula that are considered not like number by excel and therefore ordered after numeric cells if sorted?
 
Hello Davecrt,


Have you tried the "N" function? It converts values to numbers and if it doenst match a value it turns it to Zero (which would ut it to the bottom)


Try nesting it in your formula as an output as see if it helps.


Gumbles
 
Hi Gumbles


Unfortunately i have also negative values in the list, so putting(or considering, as you prefer) the "blank" cells like zeroes doesn't solve my problem, cause in this way i have a huge number of "blank" cells in the middle and then come the cells with negative values. i need a solution that allow me to reach this result 1) cells with values (positive values, true zeroes (client recorder as 0 turnover), and negative values and then 2)"blank cells"(clients that don't respect the condition so must not be present in the sheet..Is very tedious cause i'm not able to manage the records in the new sheet cause excel consider the "blank" cells like numbers..
 
Hi Davecrt,


This isnt the most elegant solution, but you could make the output:


If(ISBLANK(A1),-9999,A1)


Then Conditional format -9999to be invisible.


OR


you could put an Auto filter on that column, unselect blanks then filter largest to smallest.


Not the neatest solutions though...


Gumbles
 
You must log in or register to reply here.
Back
Top