IF Formula
Setting up a formula where Tier I = 1500, Tier II = 1200, otherwise it's 1000. This formula setup works for me with in-sheet references, but not cross-sheet references... not sure how to adjust for the cross-sheet.
=IF({MASTER TRACKING SHEET Range 1}@cell = "Tier I", "1500", IF({MASTER TRACKING SHEET Range 1}@cell = "Tier II", "1200", "1000"))
Thank you!
Answers
-
@aecross Not sure your problem description is complete. What is it in the sheet with the formula that matches to something in the cross-referenced sheet? You need to make that connection, don't you? You may need to build an index formula that connects the cross-ref range to the row of the sheet you are in.
dm
-
I've indexed this correctly and it pulls in the Tier level. My next question is how to incorporate the IF statement into an index formula? With the below i am getting an unparsable error.
=IF(INDEX({MASTER TRACKING SHEET Range 6}, MATCH([JOB #]@row, {MASTER TRACKING SHEET JOB NUMBER}, 0), {MASTER TRACKING SHEET Range 1}@cell = "Tier I", "1500", {MASTER TRACKING SHEET Range 1}@cell = "Tier II", "1200", "1000"))
-
@aecross You want your IF to do this: IF (<the INDEX statement, that returns some result> EQUALS <your condition of "Tier 1"> COMMA ...
I can see that the INDEX statement doesn't close properly. I think you need a closing bracket here (at the end):
IF(INDEX({MASTER TRACKING SHEET Range 6}, MATCH([JOB #]@row, {MASTER TRACKING SHEET JOB NUMBER}, 0))
and then you need to evaluate what it returns against your condition.
dm
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!