Unwrapped text won't flow into linked cells

Question

I have linked cells that have text flowing to empty adjacent cells in the original.

In the destination sheet, the empty cells showed 0s and the flowed text disappeared.

Suppressing the 0s in Excel options didn't fix the problem, it appears the link itself in the destination cells stops the wrapping.

The only way to see the text is to delete the link, but then it wont update if I change the original.

Does anyone know a workaround to force the destination cell to flow if the original adjacent cell is empty (ie the link value returns 0)?

Or (2nd choice) to force selected destination cells to flow irrespective of adjacent cell contents?

Answer 1

I think @fixer1234 described it correct,

Text only flows in empty cells. Empty does not include 0, blank, or containing a formula that results in blank, zero, "", or whatever.

So the only option is to remove the formulas (and anything else) in those cells where you want it to flow to. (which might not help you... but that's the limitation)

Answer 2

I largely agree with the other answer — this is not something that Excel does natively.  I do, however, have a couple of suggestions:

I. Fancy worksheet functions

On the destination sheet,

• put =IF(A1<>"", LEFT(A1,5), "") into the first cell,
• put =IF(B1<>"", LEFT(B1,5), MID(A1,6,5)) into the second cell,
• put =IF(C1<>"", LEFT(C1,5), IF(B1<>"", MID(B1,6,5), MID(A1,11,5))) into the third cell,
• etc.

replacing A1, B1, C1, etc., with the references to the source sheet.  I.e., you would be doing the text (over)flowing “manually”.  This will look hideous unless you use a constant-width font, such as Courier or Consolas, and adjust the column widths to be 5 characters wide in the selected font (or adjust the 5 to match the column widths).  If you want the column widths to be different, this becomes more complicated.

If you want text to wrap when it can’t overflow, this becomes much more complicated.

II. VBA

Either:

1. Abandon the idea of using Excel’s ability to link one cell to other(s).  Define a VBA subroutine to copy the source sheet to the destination sheet. 
2. Write a VBA subroutine to do what the other answer suggests — For each cell in the destination sheet, look at the corresponding cell in the source sheet.  If the source cell is blank, set the corresponding destination cell to blank.  If the source cell is not blank, set the corresponding destination cell to a reference to the source cell.

You might want to have either of these run every time you change the source sheet; or you could do it on-demand.  You should think about how to handle source cells that evaluate to a blank string without being empty; e.g., they contain ="" or something like =IF(Q42>=100, "red hot", "").

My personal opinion: option 2 is better.

---

P.S. You might want to look at Display Blank when Referencing Blank Cell in Excel, if you haven’t already.  AFAIK, it doesn't directly answer your question, but its discussion of related topics might be useful to you.