I have a tricky problem that I want to solve formulaically; I know how it could be solved with either Find/Replace or VBA, but I really need a formula solution that just works automatically.
To simplify the problem to its simplest form, imagine cell A1 has text "BlahBlahBlah" and cell B1 has a zero value. The formula of interest is in row 2. The formula in cell A2 is: =IF(A1=0,"",A1).
I drag the formula in cell A2 to B2 and it places a null value in cell B2. However, the tricky part is that my goal is to fully display the text in cell A2 (now "BlahBlahBlah") but since it is wider than column A it will spill over into column B. However, the problem is that the null value in cell B2 still obscures the text in cell A2 and stops it from showing over cell B2 (as it would do if there were truly no value at all in cell B2). Is there some way to have a formula in cell B2 that returns literally *nothing* when the cell above (cell B1) has a zero value?
I know this specific example seems completely trivial but I have a more meaningful situation that I need to use it for, but I tried to simplify it to exclude extraneous details. Thanks in advance!
Solved by E. H. in 18 mins