Trying to Create a Dynamic Formula and need some guidance
Hi,
Working on an advanced Dynamic Formula where selections on the sheet drive the formula results where we have several different criteria driving different selections.
[Type] a selection would be either "A" or "B" AND [Milestone] selection is driving a Reference column to indicate the YEAR (ex. Milestone 1 = FY23, Milestone 2 = FY24)
If [TYPE] = A and [Milestone] = "Milestone 1" Then enter [FY23Rate]#
If [TYPE] = A and [Milestone] = "Milestone 2" Then enter [FY24Rate]#
If [TYPE] = B and [Milestone] = "Milestone 1" Then enter [FY23RateB]#
[TYPE] = B and [Milestone] = "Milestone 1" Then enter [FY24RateB]#
So essentially my question is how do i write the formula so that it will use both of the two selections to factor into the result?
Best Answer
-
Hi @GILLL023 - You'll need nested IFs with the AND function. Give this a try:
=IF(AND([TYPE], A, [Milestone], "Milestone 1"), [FY23Rate]#, IF(AND([TYPE], A, [Milestone], "Milestone 2"), [FY24Rate]#, IF(AND([TYPE], B, [Milestone], "Milestone 1"), [FY23RateB]#, IF(AND([TYPE], B, [Milestone], "Milestone 2"), [FY24RateB]#))))
Let me know if that works!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
Answers
-
Hi @GILLL023 - You'll need nested IFs with the AND function. Give this a try:
=IF(AND([TYPE], A, [Milestone], "Milestone 1"), [FY23Rate]#, IF(AND([TYPE], A, [Milestone], "Milestone 2"), [FY24Rate]#, IF(AND([TYPE], B, [Milestone], "Milestone 1"), [FY23RateB]#, IF(AND([TYPE], B, [Milestone], "Milestone 2"), [FY24RateB]#))))
Let me know if that works!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
-
Thanks so much for the feedback. Using what you sent i was able to get it working on my sheet!
-
Great!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!