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

Question

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.

You could set column A to wrap text? Sure, you'll get multiple lines, but it's a trade-off between readability and uniform display. — Andi Mohr, May 23 '17 at 10:39
I often have long titles, which would make the table look quite strange, so it is not a very good option. — Gabriel Caron L'Écuyer, May 23 '17 at 10:51
Maybe you could write a VBA macro that would delete all formulas that yield an empty cell. — Jérôme Radix, Jul 04 '19 at 09:49

score 5 · Accepted Answer · answered May 23 '17 at 10:11

5

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.

answered May 23 '17 at 10:11

teylyn

22,743

I know about Excel's behaviour with cell's overflow as very well explained by @DanHenderson in this post : link. My question is more if someone knows of a workaround since it is something that has bothered me for years. VBA can be used, but not to delete formulas. – Gabriel Caron L'Écuyer May 23 '17 at 10:43
Or, more specifically, 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). – Gabriel Caron L'Écuyer May 23 '17 at 10:54
Well, no, not with these restrictions. Maybe you want to post a suggestion on Excel.Uservoice.com. If it gets a lot of votes, it may be one of the next features the Excel team will work on. – teylyn May 23 '17 at 11:08

score 1 · Answer 2 · answered Feb 24 '20 at 21:41

If you reallllly wanted to do it, sort of,you could make a complicated formula for the leftmost cell that should handle it. It would not let the table's data be used directly in further calculations, and the second and third columns would not really be used which might disturb a user. But it'd work to show the data AND the full entry in the first column. One could remove them, which might make it more palatable to users.

Since there are explicity numerical entries and alpha entries in the first column, but NO alphanumeric entries, one could first test for that with ISTEXT(). If NOT, then one has the current formula for that column AND the formulas for the second and third columns. One would need toTEXT()` wrap each and add spaces as required for appearance, but the look could be achieved, though with a fair bit of work.

If the test succeeds, then the cell simply has the text result it now has with no reading of the second and third columns, as they have nothing, as I understand it.

If one used a given number of spaces between first/second and a different number between second/third columns, that could be a reliable basis for pulling the data from the table for use elsewhere.

However, if being used elsewhere, one could also simply use the formulas used to poipulate this table directly, not looking up something in the table, but rather doing the first level lookup in the source data. So a direct lookup rather than a lookup in already looked up material. A bit "cleaner" in a way.

score 0 · Answer 3 · answered Jul 04 '19 at 10:16

Using VBA, you can void cells containing a formula that yield nothing :

Private Sub CommandButton21_Click()
    For Each c In Worksheets("yourworksheet").Range("yourrange")
        If c.Text = "" Then c.Select: Selection.ClearContents
    Next
End Sub

Thus, adjacent cells can extend over empty cells.

score 0 · Answer 4 · answered Feb 22 '21 at 17:48

To get the look you want, you'll need to restructure your source data. Rather than having section headers like this, take the value of those section headers and put them into a new column, on every row associated with that section. It would look like this:

If you can't get your source data laid out this way natively, you could do it yourself with some creative use of VLOOKUP and/or INDEX/MATCH to find rows where column B is empty in the source data.

Then, you can take this data and create a PivotTable summarizing it, which can be manipulated into the layout you initially desired. Here's a mock-up of that (I set the Page, Rev, and Description fields to use the Tabular layout, turned off all totals, and turned off the display of expand/collapse buttons):

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

4 Answers4