Cross-sheet formulas

krfowler97

What formula can I use to look at the data in a cell on another worksheet, then populated the cell with a designated value based on the content? For example, I have a form with multiple choice options such as Excellent, Very good, Fair, Poor, etc…. I need to create a new sheet where the response of "Excellent" is changed to "1", "Very good" = 2, "Fair" = 3, etc. I thought that I had it figured out, but I get either #INVALID REF or #UNPARSEABLE.

I even tried to scale it back just to get a simple formula to work, but still no luck

=IF({Adult Health Screening Q1 @row1} = "Very good", "2", "1")

=IF({Adult Health Screening Q1}1="Very good","2",["-1"])