Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

THANKS to all the Formula Gurus!!!

Options
✭✭✭✭✭✭
edited 12/09/19

Dear Fellow community,

While I know Excel rather well..VLOOKUP, PIVOT TABLES, etc, the LOGIC and IF formulas sometimes cause me trouble. Anybody with me.??

OK.. so I'm doing what my daugther calls a "humble brag"  cause I'm tooting my horn, but giving credit to others.....

Today (with lots of help and patience), I was able to create a (working) formula that contains the IF, AND and ISDATE commands.

=IF(AND([DOCUMENT TYPE]1 = "Termination Request Form", ISDATE([COPY TO OSP]1)), TODAY(), "")

And if that wasn't enough, I was able to create a formula with SIX (6) NESTED IFS!!!!

It only took me 2 hours... and 30 minutes of that was finding where I was missing a darn comma!!

=IF([DOCUMENT TYPE]11 = "Modification Request Form", [COPY TO OSP]11 + 49, IF([DOCUMENT TYPE]11 = "Renewal Request Form", [COPY TO OSP]11 + 49, IF([DOCUMENT TYPE]11 = "Pre Approved Vendor Request Form", [COPY TO OSP]11 + 49, IF([DOCUMENT TYPE]11 = "Sole Source Procurement Request Form", [COPY TO OSP]11 + 49, IF([DOCUMENT TYPE]11 = "Non Solicitation Request Form", [COPY TO OSP]11 + 49, IF([DOCUMENT TYPE]11 = "Interagency Agreement Request Form", [COPY TO OSP]11 + 49, ""))))))

So I want to thank several of you who gave me inspiriation:  such as Travis, John Sauber, John Hinkle, Brett Evans, J. Craig Williams and Atus Bartal!!   And the Formula Example Sheet and FAQ!!  There are others, but when I grow up, I want to be like these guys!

So for those like me who need some encouragement with formulas....keep working at it!!

Tim

Tags:

• Employee
Options

Good work Tim! Keep it up and soon I will be asking YOU for formula help!

• ✭✭✭✭✭✭
Options

Thanks Travis... we'll see.

On the first formula, I did have to add another document type so I added an AND condtion. Plus with the help of Ted in SS Support, I added a -1 to the TODAY() so that the alert would go out as needed.

=IF(AND([DOCUMENT TYPE]1 = "Termination Request Form", ISDATE([COPY TO OSP]1)), TODAY() - 1, IF(AND([DOCUMENT TYPE]1 = "Computer-Related Request Form", ISDATE([COPY TO OSP]1)), TODAY() - 1, ""))

• ✭✭✭✭✭✭
Options

Great job Tim.

Craig

• ✭✭✭✭✭✭
Options

That's great, Tim!

Thanks for the acknowledgement, but I believe we learn a lot - and as you put it, give ang get inspirations and encouragements - from each other.... This is what we really must keep working at!

Atus

• Options

Whoa.

This discussion has been closed.