Question

In the range C4:C37, create a data validation rule that accepts only LIST values from the range I4:I7In the range C4:C37, create a data validation rule that accepts only LIST values from the range I4:I7

I highlighted cells C4:C37. Went to DATA, DATA VALIDATION. In SETTINGS, under ALLOW, I clicked on LIST. Then in SOURCE I put in, I4:I7.

It does not work.  How do I enter I4:I7 into the SOURCE?

Answer 1

Solution:

Step 1 : Give random values in the range I4:I7 like in the below image.

B C D E "T G H 1 2 3 4 5 3 5 81 6 7 421

Step 2 : Select the range I4:I7 and go to insert and click on table and diologue box appears as below and click on OK.

B С D E F G H 1 2 3 4 3 5 50 6 7 81 421 х 8 ? Create Table 9 10 Where is the data for your table? E$I$4:$I$7 11 12 My table has headers 13 OK 14 Cancel 15

Step 3 : Select the range I4:I7 and go to formulae tab and then go to define name and give the details same as shown in the below image. I have given Range as my name.

B С D E F G H 1 2 3 4 Column1 New Name ? X 5 3 6 5 81 7 Name: Scope: Comment: Rangel Workbook < 8 42 9 10 11 12 13 Refers to: -Table2[Column] 14 OK Cancel 15

Step 4 : Select C4:C37 and go to Data and then go to Data validation and enter the details as below and give =Range in source and we will have drop down in C4:C37 to select the values present in I4:I7.

B c D E F G н 1 2 ? Data Validation X 3 4 Settings Input Message Error Alert lumn1 Donna 5 3 6 5 Validation criteria Allow: List 7 81 8 Ignore blank In-cell dropdown 42 Data: 9 between 10 11 Source: =Range 12 13 14 15 Apply these changes to all other cells with the same settings 16 Clear All OK Cancel 17 18 19 20 21 72