Is there a workaround that would equate to the IN Operator in MS Access?
I'm using Document Builder to populate an HR form that is used for multiple purposes. I have a single select drop down column with 17 pre-defined choices. Several of those choices require a new position number (PSN) to be entered and many more do not. In the instances that do not, I would like to create a formula to copy the value from the current PSN without having to manually input or copy/paste and avoid errors like the one I just made.
I'm hoping there is a better way than nesting multiple If statements. I plan to at least visually account for what is needed when completing the form with Conditional Formatting. Just thought it wouldn't hurt to ask.
Have a great day!
Best Answer
-
You don't need to use multiple IF statements, you could use one IF along with an OR statement.
Or you create a lookup sheet with a list of the 17 choices, and then a column with data that is either blank or "PSN Required" or something. So that field will say PSN Required for every relevant choice. Then use INDEX and MATCH to bring that value into your main sheet, with a formula that basically says if this formula is bringing back PSN Required, you put the value of the PSN field.
The formula will look like this:
=IF(INDEX({REQ_YN}, MATCH(Choice@row, {CHOICE}, 0)) = "PSN Required", PSN@row)
Where {CHOICE} is the Choice list in the lookup sheet and {REQ_YN} is the field that is either blank or "PSN Required" in the lookup sheet.
Answers
-
You don't need to use multiple IF statements, you could use one IF along with an OR statement.
Or you create a lookup sheet with a list of the 17 choices, and then a column with data that is either blank or "PSN Required" or something. So that field will say PSN Required for every relevant choice. Then use INDEX and MATCH to bring that value into your main sheet, with a formula that basically says if this formula is bringing back PSN Required, you put the value of the PSN field.
The formula will look like this:
=IF(INDEX({REQ_YN}, MATCH(Choice@row, {CHOICE}, 0)) = "PSN Required", PSN@row)
Where {CHOICE} is the Choice list in the lookup sheet and {REQ_YN} is the field that is either blank or "PSN Required" in the lookup sheet.
-
Thank you! I tend not to think of sheets on such a micro level but makes perfect sense.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 142 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!