Cross-sheet formulas
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"])
Answers
-
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:
hope it helps.
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time!
MASA Consult - Your Aligned Smartsheet Partner
Find us on LinkedIn!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!