Creating Formula from a linked sheet


Need Help.

I have two sheets- Sheet1, has all the Survey Values (i.e, Yes, No, NA), and Sheet2 will have all the Formula.

I want to assign a numerical value on the answers of Sheet1. For instance, Yes= 15, No= 0, N/A =0, and have them all populate in Sheet 2.

So here is my formula in Sheet2

=IF({Sheet1} = "Yes", 15, IF({Sheet1} = "No", 0, IF({Sheet1} = "NA", 0)))

But I am missing something.

And each cell in Sheet 1 will have different numerical value for "Yes, No, NA", so I can't do a blanket assignment of numerical values (for example column 2 and row 2 cell will have answers "Yes= 5, No =0, NA= 0)

Thanks in advance.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!