# Creating Formula from a linked sheet

Options

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)

• ✭✭✭✭✭✭
Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

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?

• Options

@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.

• ✭✭✭✭✭✭
Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!