Hi Tom,

One line in the formula bar takes as much characters as your screen/window size allows. When it reaches the end is automatically wraps to the next line. It shouldn't happen randomly, though. Nothing you can do about that.

Riny

Hi Riny, I'm just curious.

When I expand the Formula bar to see a large formula it always 'remembers' the last size.

I have one file where the formula covers 10 rows. It's formula added using vba and line feeds and carraige returns so that I understand, bur is there a way to make this dynamic?

Not remember the last size but adapt to the number of lines permitted?

Hope my question makes sense, the image shows the formula and if I click an empty cell and press the arrow to expoand the formula bar i's the same size even though it's empty.

Noticed that as well. The size of the formula bar depends on the last used setting. Don't know if there's anything you can do about that. It's just on click on the arrow to reset it, so I'm not bothered with it.

Thanks for the feedback Riny.  I'm still a bit confused.  Here is an example:

=XLOOKUP(B8,tMthlyRev[Month and Year],tMthlyRev[Trailing 3-Month Average_____________________________
for Cash Receipts],,-1,)/1000

There is plenty of space, more than enough for the text on the second line, in the menu bar to the right of "...Average" (I added the red line to indicate this).  There is no need for the line break yet Excel sees fit to add one.  To add to my confusion, if I try to manually correct the problem (by deleting space after Average to bring the remaining text back to the first line), excel returns an error message indicating a syntax problem.

Hi Tom,

I suspect this is how the table column name is formatted. Does the column named [Trailing 3-Month Average for Cash Receipts] have line breaks in the actual column name?

If you remove the space between 'Average' and 'for' in the column name and then add it back in, does that fix the issue?

Mynda

Who knew?  Thanks, Mynda.  You nailed it.  Cells referenced in the formula have column headers that have Wrap Text applied, and the break is precisely where the break in the applicable column header is.

If I run into this again I'll know what to do. If I can't live without the Wrap Text in a source's column header, I'll deal with the wrapping formula.  If the Wrap Text is less critical than having easy-to-read formulas, I can remove the Wrap Text.  

It's good to have choices. Thanks, once again, for solving this riddle for me!

Glad I could help, Tom