Formula assistance. First 4 parts working, need assist with last 3 parts.
I am needing assistance with 3 formulas to complete a project. I cant seem to get this one to work in entirety. I can get the first 4 parts to work, however, not the last one shown here in bold and have one more part to add:
=IF([25. Assurance project type for set up]@row = "AUP - Verif", 3, IF([25. Assurance project type for set up]@row = "AUP - Parity", 3, IF([25. Assurance project type for set up]@row = "AUP - Lease Suff", 3, IF([25. Assurance project type for set up]@row = "AUP - Landfill Assistance", 3, IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection "(AR-C 80)"", 4)))))
Need to add:
IF([25. Assurance project type for set up]@row = "CD - Comp GAAP_w/o discl", NA, IF([25. Assurance project type for set up]@row =" CD - Comp Cash basis_w/o discl", NA)))))))
The other two are a bit more complicated. Any help or guidance is greatly appreciated! TIA!
Best Answers
-
Your original formula has an issue because of the quote marks, you need to replace those with CHAR(34) INSTEAD.
As well, i simplified your first part of the formula using CONTAINS, and added the items you wanted added:
=IF(CONTAINS([25. Assurance project type for set up]@row, "AUP - Verif AUP - Parity AUP - Lease Suff AUP - Landfill Assistance"), 3, IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection " + CHAR(34) + "(AR-C 80)" + CHAR(34), 4, IF(CONTAINS([25. Assurance project type for set up]@row, "CD - Comp GAAP_w/o discl CD - Comp Cash basis_w/o discl"), "NA", "")))
-
So then you dont need the quote marks you originally showed:
Use below revised:
=IF(CONTAINS([25. Assurance project type for set up]@row, "AUP - Verif AUP - Parity AUP - Lease Suff AUP - Landfill Assistance"), 3, IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection (AR-C 80)", 4, IF(CONTAINS([25. Assurance project type for set up]@row, "CD - Comp GAAP_w/o discl CD - Comp Cash basis_w/o discl"), "NA", "")))
Answers
-
Your original formula has an issue because of the quote marks, you need to replace those with CHAR(34) INSTEAD.
As well, i simplified your first part of the formula using CONTAINS, and added the items you wanted added:
=IF(CONTAINS([25. Assurance project type for set up]@row, "AUP - Verif AUP - Parity AUP - Lease Suff AUP - Landfill Assistance"), 3, IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection " + CHAR(34) + "(AR-C 80)" + CHAR(34), 4, IF(CONTAINS([25. Assurance project type for set up]@row, "CD - Comp GAAP_w/o discl CD - Comp Cash basis_w/o discl"), "NA", "")))
-
That worked! Thank you so very much!!
-
I tried this again today and the following part does not work within the recommended formula from Leibel S: "IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection " + CHAR(34) + "(AR-C 80)" + CHAR(34), 4," any idea why? Your formula returns the correct "3" or NA rating, however, not the "4". Thank you for any help!
-
What is in the [25. Assurance project type for set up] field?
Send screenshot
-
Thanks for your help!
-
So then you dont need the quote marks you originally showed:
Use below revised:
=IF(CONTAINS([25. Assurance project type for set up]@row, "AUP - Verif AUP - Parity AUP - Lease Suff AUP - Landfill Assistance"), 3, IF([25. Assurance project type for set up]@row = "Bond Issue - Comp Forecast/Projection (AR-C 80)", 4, IF(CONTAINS([25. Assurance project type for set up]@row, "CD - Comp GAAP_w/o discl CD - Comp Cash basis_w/o discl"), "NA", "")))
-
Perfect! This works! Thank you so much for the help and the simplification lesson! Much appreciated!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!