0

I have a list of user and data validation mapping in column A and B

User         Data Validation
Customer          Jim
Customer          Pam
Customer          Tim
Employee          Beth
Employee          Larry
Employee          David
Manager           Krista
Manager           Bob

Next I have a list of the User and Data Validation required in column D and E

User          Data Validation
Customer
Employee
Manager
NA
Employee

I'm attempting to apply Data Validation from the first table to the second table.

Customer would be able to select (Jim, Pam, Tim). NA would be blank. Employee would be able to select (Beth, Larry, David).

The tricky part is we may add or change User functions. We may also add items to the current Data Validation list from table 1.

1 Answer 1

1

You said that you're trying to "apply Data Validation from the first table to the second table.". I'm not sure what this would have to do with Apps Script (except that you can define Data Validation with an Apps Script). But in this question you appear to have the wrong idea about Data Validation "Criteria". This answer is intended to help you better understand this.

When using Data Validation there are two key fields:

  • Cell Range - the cell in which you want the user to select choices (such as, Customer would be able to select (Jim, Pam, Tim). NA would be blank. Employee would be able to select (Beth, Larry, David))
  • Criteria - The choices that are available in the "Cell Range"

Compare the layout below to your layout.

  • the criteria are in columns - one for each criteria group.
    • this enables you to easily add/modify/delete criteria for any given group.
    • It also enables you to insert more columns if you want to add new criteria
  • it is possible to put the criteria in rows (this is just as good as columns). But this wouldn't work in your situation because your want the Data Validation cell ranges to be in Column E.
  • note that the Criteria in the sample is $A$2:$A$20 - this allows to you add more options without having to redefine the criteria range. And even though there are 19 cell in the criteria, only the cells with values are choices in the drop down.

Customer Data Validation

customer


Layout of Cell Ranges and Criteria

snapshot


Data Validation - Cell E2

data validation

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.