Formula Entry Formatting

jsquaredz

I'd like to suggest an enhancement for the formula bar in Smartsheet. As it stands, users are unable to expand the formula bar or input/format multi-line formulas directly. This limitation is particularly challenging when dealing with complex formulas, where keeping track of parenthesis pairings and overall structure is crucial.

Currently, the only workaround is to draft formulas in a separate editor and then paste them into the formula bar. However, this method results in the loss of any formatting once the formula is saved, which can lead to errors and confusion.

Therefore, I propose allowing the formula bar to be expandable and capable of accepting direct multiline input with formatting retention. This change would significantly enhance the user experience, especially for those of us dealing with intricate formulas, by improving readability and reducing the likelihood of errors.

Which is easier to understand when you are keeping track of multiple levels of nesting and arguments?

Sherry Fox

This is a fantastic idea. By default I have my formula bar in Excel set for about 5 rows, where everyone else uses the default one row. Having the ability to expand the formula bar is a huge asset. Most of my projects deal with multiple formulas, especially linking to other sheets.

jsquaredz

I added a couple images to further illustrate how difficult to understand/write a single line on non-formatted, nested code. For me it is difficult to keep track of the closing braces, parens, etc. if they are not formatted. Being able to type formatted code is important.

Thanks for voting the idea up.

Samuel Mueller

Great feedback. I also run into a lot of issues because the entry bar is on the same row as the formula. So if I'm doing a same row formula, I can't click the cell I need to because the formula entry box is in the way. It needs to be beneath the row.

spauliszyn

I never thought of this format for editing complex formulas, but once I've seen it how can you I back?!?!

Even if the text was edited in a multiline text box without "smart indenting"—and Smartsheet retained the extra spaces and CR's—that would be miles ahead.

Sherry Fox

https://community.smartsheet.com/discussion/comment/417215#Comment_417215

At the top, in the original post, there is a yellow bar, and on the left it (currently) says 6 votes (in a gray box). Click on that box to cast your vote, and you will see the number change to 7.

spauliszyn

https://community.smartsheet.com/discussion/comment/417231#Comment_417231

But of course Sherry, I already did that when I commented. Maybe it doesn't report it globally within the minute I voted--be patient. Sometimes I even up-vote without commenting, but if I really like the idea I add a comment.

ljkeefe1

There needs to be a way to view formulas in an expanded view. Currently, it's tough to see extra commas or discern between a curly bracket and regular and so on. This is especially difficult with long and/or complicated formulas. Without a formula helper like in Excel, being able to expand the view is crucial and save our eyesight. 😀

Brian_Richardson

I would LOVE to see a significant enhancement to the experience of building formulas. Formulas are the single most common area of confusion, and the first "step" of any builder toward enabling automation and leveraging Smartsheet.

Therefore, the formula builder could use some help to take it from "just as bad as excel, all in one cell" to "amazing and easy-to-use formula builder!"

Formulas should be able to be written down like code, broken out onto multiple lines at the writer's discretion onto whatever lines makes sense within the cell for easier reading. Builder box should auto-indent arguments.

Taking the formula from this mess:

=IF(OR(ISBLANK([Assigned To]@row), ISBLANK([Expected Hours of Work]@row)), "", SUM(COLLECT([Daily Hours]:[Daily Hours], [Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, <=Finish@row, Finish:Finish, >=Start@row)))

To this easier-to-follow format:

=IF(
      OR( 
            ISBLANK([Assigned To]@row), 
            ISBLANK([Expected Hours of Work]@row) 
      ),
      "", 
      SUM(COLLECT([Daily Hours]:[Daily Hours], 
            [Assigned To]:[Assigned To], [Assigned To]@row, 
            Start:Start, <=Finish@row, 
            Finish:Finish, >=Start@row
            ) 
      ) 
)

When someone hits = in a cell, the text entry area could immediately reformat to a building box that would accept Enters, Tabs, Spaces to reformat the formulas without changing their functionality.

Additionally, this should open up options for dynamic error correction in formulas, much like code editors show red highlights when the formula as-written has a problem like a missing argument, a missing closing parenthesis, a function name that doesn't exist or is misspelled, an extra comma, and more very common issues that are nearly impossible to find in a long single string formula.

AWentz

100% agree!!!!! Just started using Smartsheet and was surprised to find that the formula 'box' was just that, a tiny little box where you would struggle to write complex formulas. I imagine there is much time wasted by end-users when writing and/or troubleshooting formulas because of the 'tiny little formula box'. Such a primitive feature and is sub-standard in today's environments.