Employee In Take Form: Trying to auto check selections based on 1 and 2 column criteria.

I have many columns created with a checkbox. Ten columns are labeled as required devices for employee types. [Employee type column named "Role"] When a certain employee type is entered in the "Role" column the "Devices" column check boxes would be auto selected showing what devices are required for that Role. Example: If "Role" = "Physical Therapy Field Nurse" then the result would be check boxes selected for Laptop, Docking Station, Portable printer, iPhone, ID Badge.

A second set of check boxes needing two column criteria would consist of Twenty columns each with a unique Email Distributuion List name to be checked based on the "Role" column and a "Team# column. Example: If "Role" = "Physical Therapy Field Nurse" and "Team#" = 3 then the result would be check boxes selected for the following Email Distribution Lists - PT Field Nurse Team 3, General Nursing Team 3, Scheduling Team 3.

Any guidance is much appreciated.

Find more posts tagged with

Sheets

functionality

Cell linking

Accepted answers

All comments

Paul Newcome

Are you able to provide some screenshots for reference?

Tom Dolan

Thanks for your input.

DKazatsky

Hi @Tom Dolan

One way to accomplish this is to setup a sheet with all the criteria you have and do a VLOOKUP from the Intake sheet, based on Role. This would require a lot of setup initially but should do what you want.

Here is an example of the criteria sheet (just with devised for now):

Here is an example of the Intake form:

As you can see, as I choose a role in the Intake form, the appropriate boxes get checked.

The formula looks like this: =IFERROR(VLOOKUP(Role@row, {Employee Intake Criteria Range 1}, 2, false), 0)

{Employee Intake Criteria Range 1} = The entire range from the criteria sheet, select the entire columns when creating the range.

2 = The return column number from the criteria range, this needs to be adjusted for each column.

To incorporate the emails, just keep adding columns to the criteria sheet and expanding the range to be inclusive. Also, be sure to increment the return column number in the VLOOKUP formula in the new column in the Intake form.

I have no idea if this is the "best" way, but it is one way.

Hope this helps,

Dave

Tom Dolan

Dave, you couldn't have said it better "I have no idea if this is the "best" way, but it is one way." I have been thinking the same thing with all the initial setup that is required.

Paul Newcome

Another way that would be less work on the back-end of the sheet (but still requires plenty of work on the setup) would be to use IF statements.

Equipment:

=IF(OR(Role@row = "Physical Therapist", Role@row = "Occupational Theapist"), 1)

Email:

=IF([Team #]@row = "Team 1", IF(OR(Role@row = Physical Therapist", Role@row = "Occupational Therapist"), 1))

DKazatsky

One thing to consider is future changes. With the use of complex IF statements, they all need to be reworked if device/email assignments change. With the use of VLOOKUP and criteria sheets, changes can be made quickly by checking/unchecking where needed.

Food for thought.

Paul Newcome

I agree that reference tables make future changes easier. The only real downside to reference tables is that they can slow things down at scale.

I do however suggest INDEX/MATCH in place of VLOOKUP as it is more flexible if the structure of the reference sheets gets changed and can be easier to manage if you rename your cross sheet references when creating them to show what exactly they are looking at.

It is also pretty easy to incorporate the COLLECT function when needing to match on two separate columns such as Role and Team.

Sign in to join the conversation!

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.