Is there a workaround that would equate to the IN Operator in MS Access?

Jim Rood

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!

James Keuning

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.

James Keuning

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.

Jim Rood

Thank you! I tend not to think of sheets on such a micro level but makes perfect sense.