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!!!
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
Comments
-
Good work Tim! Keep it up and soon I will be asking YOU for formula help!
-
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, ""))
-
Great job Tim.
Craig
-
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
-
Whoa.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives