What is the longest formula you've created?

SteyJ

As the title suggests, I am very curious to see how far some of you all have pushed the limits of Smartsheet formulas. I think it will be interesting to see everyone's longest formula, and what they created this formula for.

I'll go first: A person on the Smartsheet community was requesting a column to have employees names formatted a certain way. They wanted the first letter after a hyphenated last name to be capitalized. Essentially, converting jacob stey-stey to Jacob Stey-Stey

Here's what I created for them (which just so happens to be my longest formula)

=IF(CONTAINS("-", [Submitter User Name]@row) = 1, UPPER(LEFT([Submitter User Name]@row, 1)) + 
MID([Submitter User Name]@row, 2, FIND("_", [Submitter User Name]@row) - 2) + " " + 
UPPER(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row, 1) + 1, 1)) + 
IFERROR(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row) + 2, 
IF((FIND("-", [Submitter User Name]@row) - FIND("_", [Submitter User Name]@row) - 2) >= 0, 
FIND("-", [Submitter User Name]@row) - FIND("_", [Submitter User Name]@row) - 2)), "") + 
IFERROR(UPPER(MID([Submitter User Name]@row, FIND("-", [Submitter User Name]@row, 1), 2)), "") + 
MID([Submitter User Name]@row, FIND("-", [Submitter User Name]@row) + 2, 
LEN([Submitter User Name]@row) - FIND("-", [Submitter User Name]@row)), 
UPPER(LEFT([Submitter User Name]@row, 1)) + MID([Submitter User Name]@row, 2, FIND("_", [Submitter User Name]@row) - 2) + " " + 
UPPER(MID([Submitter User Name]@row, FIND("_", [Submitter User Name]@row) + 1, 1)) + MID([Submitter User Name]@row, 
FIND("_", [Submitter User Name]@row) + 2, LEN([Submitter User Name]@row) - FIND("_", [Submitter User Name]@row)))

Andrée Starå

Hi @SteyJ

I hope you're well and safe!

Interesting!

A couple of years back, I was working with a client on a solution for measuring time, and I remember hitting the limit, so I had to shorten all the column names to make it work, and there weren't many characters left.

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SteyJ

https://community.smartsheet.com/discussion/comment/425095#Comment_425095

HAHAHA that's awesome!

this is only 1200 characters, so I imagine your formula was almost 3x as big!!!!

KPH

My longest formula was actually one of the easiest and only uses one function (IF - many times). But it is an interesting (to me at least) use case. 

We have a master sheet that holds lots of project details and we use these in reports, dashboards, etc. We also produce a client-facing email that contains some details from this sheet. It is a pretty HTML email. What details are included and where they appear in the email depends on the project type. The email used to be created by manually copying and pasting more than 100 individual text strings into the HTML template and then adding hyperlinks. 

My formula is essentially chunks of HTML and IF statements. On each row in the sheet, IF certain things are true, it outputs certain bits of HTML, and within those chunks of HTML, it inserts strings of text from the row. If other things are true some of the strings it outputs become hyperlinks using URLs found in other cells within the row.

I then have a simple report with a filter that just gives me this column of HTML for the rows I need for the next email. We simply copy the entire column, paste it into Notepad with some header and footer HTML, and the email is done. One copy and paste instead of hundreds. And no accidents! 

Surely not what Smartsheet is for, but I kind of love it.

Will Jeffords

I once had to create a dashboard to help myself not forget how I created one of the most complicated formulas I ever needed. I’m sure there are more elegant ways to do what I needed, but I ended up with this to help create Deferred Revenue tools and metrics for financial modeling…this us fairly nutso, but maybe will provide some of you some chuckles :)

SteyJ

https://community.smartsheet.com/discussion/comment/436236#Comment_436236

Will, this is awesome. I love how you documented how this formula works. One thing I always recommend is naming cross sheet references to make it easier for contributors to identify which column the reference is ranging when looking over the formula(s).

Looks great though!! Thank you for sharing

Will Jeffords

@SteyJ yes! You are totally spot on with that naming recommendation! I would have helped myself even in this case if I had slowed down to make that help the clarity. Others should learn from my mistake there!!

Jamie Ide

=IF(AND(MONTH(TODAY()) = 1, [Jan Actual]@row = 0), [Jan Planned]@row, IF(AND(MONTH(TODAY()) = 2, [Feb Actual]@row = 0), [Feb Planned]@row, IF(AND(MONTH(TODAY()) = 3, [Mar Actual]@row = 0), [Mar Planned]@row, IF(AND(MONTH(TODAY()) = 4, [Apr Actual]@row = 0), [Apr Planned]@row, IF(AND(MONTH(TODAY()) = 5, [May Actual]@row = 0), [May Planned]@row, IF(AND(MONTH(TODAY()) = 6, [Jun Actual]@row = 0), [Jun Planned]@row, IF(AND(MONTH(TODAY()) = 7, [Jul Actual]@row = 0), [Jul Planned]@row, IF(AND(MONTH(TODAY()) = 8, [Aug Actual]@row = 0), [Aug Planned]@row, IF(AND(MONTH(TODAY()) = 9, [Sep Actual]@row = 0), [Sep Planned]@row, IF(AND(MONTH(TODAY()) = 10, [Oct Actual]@row = 0), [Oct Planned]@row, IF(AND(MONTH(TODAY()) = 11, [Nov Actual]@row = 0), [Nov Planned]@row, IF(AND(MONTH(TODAY()) = 12, [Dec Actual]@row = 0), [Dec Planned]@row))))))))))))