Creating Formula from a linked sheet

Joyce Y.

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.

Paul Newcome

Are you able to provide some mocked up examples of what you are wanting to accomplish?

Joyce Y.

Paul Newcome- Below is an example (Sorry, I can't figure out how to attach the file. My brain is fried right now. 😂).

Sheet 1 has all the data that I want.

Sheet2 is where all the formula I want to set things up. It should read the answers from Sheet1.

Below is an example of the formula for a particular cell. But it's currently only reading "Yes". I don't know how to add a value to a "No" and "NA" answers from Sheet1.

=IF({Sheet1 Range 1} = "Yes", 15)

I think there is an easier way, but I just can't figure it out. Thanks in advance!

Paul Newcome

Why not just put the nested IFs in the main sheet and have the score calculated there? What is the end goal for pulling all of this into a separate sheet?

Joyce Y.

@PaulNewcome- That is a good question. I actually haven't figured out how to do it.

So there is a Survey Form that gets filled out and all the "Yes/No/NA" answers will populate in Sheet 1. Each "Yes/No/NA" on each question, have different points. So on the Survey Form if a survey has Question 1 answered as "Yes"- that's 15points, if "No"=0; for Question 2 if they answered "Yes"- it's 5 points, "No"=0, etc.

I'll probably end up doing a 1:1 live tutorial.

Paul Newcome

I would suggest inserting columns on the sheet that houses the submissions. How many questions will you have?

Joyce Y.

That would probably be the easiest way. There will be 34 questions (at this time). I thought I'd be able to have a sheet with just the "yes/no/na" answers and a different sheet with the formula for a cleaner look.

With excel I was able to create a tab inside the excel file to separate the survey answers from the formula. But it seems Smartsheets doesn't have that option (or most likely, I just don't know it yet).

Thanks for your suggestions.

Paul Newcome

You can always hide the calculation columns if your concern is a cleaner looking sheet. It will also be MUCH easier to do on the same sheet as opposed to using a separate sheet.