Log In
Register
Home
Active Member
January 26, 2020
Offline
I am consistently having longer formulas wrap randomly in the formula bar. I am not talking about wrapping on purpose with Alt+Enter. I am talking about formulas wrapping without instruction. It causes me to expand the formula bar window to see the formula which, in turn, shrinks the amount of canvas for the actual worksheet.
Any help is appreciated.
See Answer
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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
October 17, 2018
Offline
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.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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.
Active Member
January 26, 2020
Offline
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.
July 16, 2010
Offline
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
Answers Post
Active Member
January 26, 2020
Offline
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!
1 Guest(s)
