# Trying to Create a Dynamic Formula and need some guidance

Options
✭✭

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

Thanks so much for the feedback. Using what you sent i was able to get it working on my sheet!

• ✭✭✭✭✭✭
Options

Great!

Amber Eakin, MSLS, M.Ed.

Adult Education Specialist | Process Improvement Enthusiast

• ✭✭
edited 04/17/23
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!