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"])

kowal

hi @krfowler97,

If I were you I would create extra sheet called dictionary for example and it would have two columns:

ColumnA ColumnB

Excellent 1

Very Good 1

etc.

then in the destination sheet I would use a vlookup (or even better index/match function) so it would look for excellent but it would fill it with number like 1.

Here you have a video how index/match works:

https://www.youtube.com/watch?v=_vMuSLpOZ_g

hope it helps.