(There might be an answer to this question somewhere, but I can’t find it. Sometimes you don’t know how to ask a question in a search field. Sorry if I missed it.)
I have a sheet that I update with new data every day and five or six columns that each have formulas to manipulate the data which is then used for a work flow for my employees. Since I’m always adding new data I have 5000 rows of formula and the data I enter is usually less (say 3000). I have a formula that returns a numerical value 1-10 in order to rank the rows in importance, and if the row is blank it enters a value of “” instead.
This is clean and empty rows look great, but it then messes with my sorting feature (a field with “” as a value is “larger” than 10. Other things I’ve chosen as a return value for a blank row (-,0) all still sort to the top when I chose either ascending or descending.
For example:
=IFERROR(VLOOKUP(J2,Table!J:L,3,0),””)
Is there a value I can put in the field in place of “”, or a format for a column, that can get all of my blank rows to stay to the bottom when I sort largest to smallest or vice versa?
Answer
Try using a non-breaking space, which can be represented in an Excel formula as CHAR(160).
So, your formula would look like this:
=IFERROR(VLOOKUP(J2,Table!J:L,3,0),CHAR(160))
Attribution
Source : Link , Question Author : Neal L. , Answer Author : MJH
