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

Are you able to provide some mocked up examples of what you are wanting to accomplish?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

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!

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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

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

I would suggest inserting columns on the sheet that houses the submissions. How many questions will you have?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

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.

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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!