Using IF/THEN for my formula
Hello,
I recently received advice on here to develop a formula that lists out all projects that individuals are billing to via cross sheet formulas. The below formula would go into the Projects column in the Enterprise Rollup.
=JOIN(COLLECT({Project CHEAT}, {Person}, Person@row), ", ")
This formula works, however, it is adding projects that are marked as "Inactive". I would like to update the formula to only calculate projects that are marked as "Active" in the 'Active ProjectCHEAT' column. I tried this formula but I get a #UNPARESABLE response:
=if({Active Project CHEAT}="Active", JOIN(COLLECT({Project CHEAT}, {Person}, Person@row), ", "),"")
Data Input sheet:
Enterprise Rollup:
Answers
-
COLLECT() can use as many criterion as you need, so just put the logic in that function instead:
=JOIN(COLLECT({Project CHEAT}, {Person}, Person@row, {Active Project CHEAT}, "Active"), ", ")
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Thank you @Jason Tarpinian ! What if I am trying to have the active be mulitple values… looking do the same thing for listing proposals but want it to show up if the status is Won, Assume Win, or Assume Lose. This is what I tried but did not work:
=JOIN(COLLECT({Proposal CHEAT}, {Data Input Proposal Person}, Person@row, {Proposal Status}, ="Won", "Assume Lose" ,"Assume Win"), ", "))
-
You can use an OR statement within the criterion. The caveat to this is that when you are checking criterion against an entire range, you have to specify to look at each individual cell using the @cell reference.
=JOIN(COLLECT({Proposal CHEAT}, {Data Input Proposal Person}, Person@row, {Proposal Status}, OR(@cell = "Won", @cell = "Assume Lose", @cell = "Assume Win")), ", ")
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!