Xlookup, If, etc formulas and the dreaded #Value! that comes with "" when pasting as values

[dogchow101]

In xlookup you can define what your error response is ~~ =xlookup(a3,b:b,c:c,<whateveryouwantyourerrorresponsetobe>,option,option). Typically I use "" as my error response. However when you copy the cell and paste as value (the result being an error state equaling "") in another cell location, or even another workbook, and try to do math on that pasted data, you always get a #Value! error. There should be an alternative option for the xlookup error response element that allows excel to see NOTHING. However that isn't the case and everyone wants the world to redo miles and miles of spreadsheets to function properly and output zeros and use conditional formatting. That doesn't work for everyone and also they don't necessarily want to see a zero in the cell data or under filter, they want an empty box ( "" does not equal 0 and in some cases under usage for accounting etc this is actually VERY IMPORTANT!!! ). So I found the fastest workaround to get through the quandry of having to execute the F2 function through even thousands of those lovely #Value! entries.

Just FYI This most likely won't work in contiguous row entries (only in contiguous column entries) all that well, but it works in columns lovely.

You can filter the column of #Value! entries down so that only those entries show. Swipe highlight, or Ctrl + down arrow (which should stop at the end of the data set). Here's the tricky part, down try to double click into the top cell in the cell stack, actually click in the formula edit box, only once, now you are in edit mode for the top most selected cell. At this point, and here's the loveliest part of this all, DON'T JUST HIT ENTER, use Ctrl + Enter, the Ctrl applies the action to all the selected cells and since you are in a filter, excel ignores all of the filtered away cells.

Voila!! all of the #Value! entries are gone!!!

Thank you very very much, I love you all, I'll be here all week!!

 

[jasonb75]

and try to do math on that pasted data

Not if the formulas are written correctly. The methods for ignoring "" cells varies depending on whether it is a simple formula or an array in another function but often the use of the N() function is enough to fix it.

 

[dogchow101]

When you copy any and all types of formulas they always result in an output. NO MATTER WHAT. The output may or not be displayed. I have searched and tried every posible non-VBA solution including the use of CHAR() N() etc etc and all result in an outcome that when copied and pasted as values carry data to the destination cell. When that data is operated on by a mathematical equation you receive the #VALUE! error. I challenge you to do exactly what i am looking to do with your N() solution and operate a mathimatical equation on the PASTED DATA and get a non errored result with the destination cell being EMPTY. NOT JUST DISPLAYED AS EMPTY BUT ACTUALLY EMPTY.

 

[Fluff]

How can the destination cell be empty? It's got a formula in it.

 

[dogchow101]

@Fluff. Exactly! But...... The big brains at Microsoft should be able to program something that makes this work. Thats the primary point. Secondarily though the work around gets you through the issue fast and I wanted to post something about it because no one really had. And all of the options resulted in either redoing a million other

 

[dogchow101]

equations and/or formats. This allows you to process through it fast.

 

[Fluff]

You issued a challenge to use a formula, but for the destination cell to be empty. The destination cell is the one with the formula you challenged us to use.
ergo It's not nothing to do with MS, a cell CANNOT be empty if it has a formula in it, regardless of the formula result.

 

[dogchow101]

@Fluff the destination cell is VALUES only. The value output of "" is not empty. I need the value output to be empty.

 

[dogchow101]

The scenario is not just in a single cell as well. This scenario is in a stack of 200+ cells and in happens in 5 different sets of data. So for me to single delete the equations or or single execute F2 is time consuming.

 

[jasonb75]

I need the value output to be empty.

There is a difference between need and want! If you want it to be empty then you need to delete the formulas that are returning null strings before copying.

I challenge you to do exactly what i am looking to do with your N() solution and operate a mathimatical equation on the PASTED DATA and get a non errored result with the destination cell being EMPTY. NOT JUST DISPLAYED AS EMPTY BUT ACTUALLY EMPTY.

The N() solution is for an equation to work with a cell that appears to be empty but contains a null string, which is what you will have when you copy and part cells that contain formulas. If you're deleting the #VALUE! errors then there is the possibility that you are deleting valid results. If there should not be a result when a cell is empty then removing the null strings at the source is going to give you invalid results because an empty cell has a default value of zero.

Regardless of that, instead of filtering and doing one column at a time, the logical way to deal with the #VALUE! errors would be to select the range that contains the #VALUE! errors, then press Ctrl g > Special > Formulas > Errors which will work with any range, not just a filtered column.