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,
=IF(A1<>"", LEFT(A1,5), "") into the first cell,
=IF(B1<>"", LEFT(B1,5), MID(A1,6,5)) into the second cell,
=IF(C1<>"", LEFT(C1,5), IF(B1<>"", MID(B1,6,5), MID(A1,11,5)))
into the third cell,
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.
- 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.
- 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.