worksheet function - Excel - Overflow the text of a cell to an adjacent cell that contains a formula

I have a table filled with datas that comes from other tables. The formulas in the cells of my table are quite simple (reference to a cell in another worksheet).

In my table, I have "Title" rows (rows 57 and 64 in that case) that contains only one text value that I would like to overflow to adjacent cells since the adjacent cells in those rows will always be empty.

If the values in the table where entered manually, there would be no problem and the cell would looks like exactly like I want it as in the image below.

The value of the first cell in row 57 does not overflow because the adjacent cell containt a formula (the formula is basically "=OtherCell" where OtherCell does not contain any value (more specifically, a value of ""))

It is important to note that the position of the "title" row changes and occurs in many rows in the table since the data are automatically extracted a database.

How can I achieve this...

• Without using manual intervention (those are automated reports, I can't delete formulas because the next report generated will be erroneous)


• VBA can be used, but not in a way that modify the table in an "irreversible" way (in a way that makes that the next time the sheet is calculated, the data is erroneous), for the same reason as manual intervention.

I use Excel 2013, but I believe this question applies to 2007+ versions of Excel.

Answer

The short answer is: You can't.

The long answer is that only truly empty cells will allow text from the next cell to the left to spill over and display in that cell.

This is just how Excel works.

If you specifically exclude manual intervention, deleting formulas, as well as VBA, there's really nothing left other than accepting the defaults, which is that a formula in a cell to the right will prevent text from spilling over to the right.

Don't shoot the messenger.