Trying to Create a Dynamic Formula and need some guidance



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

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!