If/and referencing multiple sheets
Looking to express the following:
If [ID#] in sheet A is equal to [ID#@Row] (in sheet B),
AND [Progress] in sheet A is between 90 and 100, show "Published" in [Progress] in sheet B,
If [Progress] in sheet A is between 15 and 89, show "In Progress" in [Progress] in sheet B,
If [Progress] in sheet A is between 0 and 14, show "Not Started" in [Progress] in sheet B
Have been unsuccessfully been trying with different variations of:
=IF{ID# Range1), [ID#] @Row, AND(({Progress Range 1} <100, >90), "Published"), (({Progress Range 1} <89, >15), "In Progress"), (({Progress Range 1} <100, >90), "Not Started")))
Answers
-
Hi @MB1234,
Something like this should do the trick for you:
=IF(INDEX({Sheet A Progress}, MATCH([ID#]@row, {Sheet A ID#})) >= 90, "Published", IF(INDEX({Sheet A Progress}, MATCH([ID#]@row, {Sheet A ID#})) >= 15, "In Progress", "Not Started"))
Sample Sheet A data:
Sample Sheet B output:
Hope this helps, but if you've any problems/questions then just post! 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!