How to Use Data Validation List from Another Sheet (6 Methods)

We can restrict data input by applying Data Validation. In this article, we’re going to show you 6 examples of how to apply the Data Validation list from another sheet in Excel. We can see a snapshot of our dataset. Currently, we’ve Product and Category columns in the dataset. This dataset will change from time to time in our step-by-step guide.

Download Practice Workbook

6 Ways to Use Data Validation List from Another Sheet

1. Using Data Validation List from Another Sheet to Create a Drop Down List

We’ve 2 columns in this dataset. Product purchased by a customer will be inserted using the Data Validation list from another sheet. Let’s jump into the steps.

Steps:

• Firstly, select cell range B5:B11 from the “dropdown” sheet.
• Then from Data tab >>> Data Validation.

After that, the Data Validation dialog box will appear.

• Then, select List from Allow drop-down menu.

Note:  Ignore blank and In-cell dropdown should be checked by default. If not, put a tick mark beside those.

• After that, click on the Source box.
• Then, select the “source” sheet.

• After that, select the cell range B5:B11. This is our validation list.
• Finally, press OK.

We can see an arrow icon.

• Click on that arrow icon.

We can see our Data Validation list from another sheet.

• After that, pick anything from the list.

The value will be inserted into our dataset.

Here, we’ve filled our dataset using the Data Validation list from the other sheet in Excel.

Read More: How to Make a Data Validation List from Table in Excel (3 Methods)

2. Apply Date Range by Using Data Validation List from Another Sheet

Now our dataset has an empty stock date column for the products. We’ll populate this using the Data Validation list. This time, we’ll use the Date between option.

Steps:

• Firstly, we’ll select cell range C5:C11.
• Secondly, bring up the Data Validation dialog box.
• Thirdly, pick these settings:
  • Allow: Date.
  • Data: between.

• Then pick the cell reference from the “source” sheet.
  • Start date: B14 cell from “source” sheet.
  • End date: B15 cell from “source” sheet.

Sometimes, we need to use an absolute cell reference in our data. This will ensure our data don’t get changed when moving to another cell.

• Finally, press OK.

Now, if we enter anything other than our defined criteria, then we’ll get an error message. This message can be modified.

We can change the message by going to the Error Alert tab from the Data Validation dialog box.

• Firstly, Type something in the Title: This is an optional task.
• Secondly, customize the message by typing in the Error message:
• Finally, press OK.

We can see our custom error message is working flawlessly.

We can manually type the dates into the rest of the fields. A warning message will be shown if we type something beyond the date range. That is how we can apply the Data Validation list to restrict our cells.

Read More: How to Use Named Range for Data Validation List with VBA in Excel

3. Fixing a Time Limit by Utilizing Data Validation List from Another Sheet

In this example, we’re going to use the Data Validation list to limit our time input in the cells. Moreover, that list is on another sheet. Our aim is to input time within our criteria in the Stock Time column.

Steps:

• Firstly, select the cell range D5:D11.
• Secondly, bring up the Data Validation dialog box.
• Thirdly, choose these options:
  • Allow: Time.
  • Data: between.
  • Start time: cell F14 from the “source” sheet.
  • End time: cell F15 from the “source” sheet.
• Finally, press OK.

Now, we can populate the Stock Time column with the time between 8 AM and 5 PM.

Related Content: Create Data Validation Drop-Down List with Multiple Selection in Excel

Similar Readings:

• Autocomplete Data Validation Drop Down List in Excel (2 Methods)
• Excel Data Validation Based on Another Cell Value
• Excel Data Validation Alphanumeric Only (Using Custom Formula)
• Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)

4. Get Greater Number by Applying Data Validation List from Another Sheet

In this example, we’ve added yet another column to our dataset. We’re going to use greater than criteria for the whole number for applying the Data Validation list.

Steps:

• Firstly, select cell range E5:E11.
• Secondly, from the Data tab, bring up the Data Validation dialog box.

You may get a similar warning message. Click NO.

• Then, From the dialog box, pick these:
  • Allow: Whole number.
  • Data: greater than.
  • Minimum: cell F7 from the “source” sheet.
• Finally, press OK.

After that, we can put values that are greater than 0 in the Amount Sold column. By using this Data Validation list, we can avoid typing wrong values.

Related Content: How to Remove Blanks from Data Validation List in Excel (5 Methods)

5. Applying Data Validation List from Another Sheet to Limit Text Length

We’ve got a new dataset for this example. We’re going to input the Salesperson associated with a Product sale. We’re only going to input short names. Therefore, we need to limit the number of characters that can be placed in those cells.

Steps:

• Firstly, select cell range C5:C11.
• Secondly, bring up the Data Validation dialog box from the Data tab.
• Thirdly, pick these options:
  • Allow: Text length.
  • Data: between.
  • Minimum: cell B40 from “source” sheet.
  • Maximum: cell B41 from “source” sheet.
• Finally, press OK.

Thus, we can use our Data Validation list and fill the rest of the cells.

Read More: How to Apply Multiple Data Validation in One Cell in Excel (3 Examples)

6. Using Data Validation List from Another Sheet for Creating a Dependent Drop Down List

In this, we’re going to create a dependent Drop Down menu by using the Data Validation list. We’re going to use the INDIRECT function and Named cell range here.

Steps:

At first, we’re going to create a Named Range.

• Firstly, select the cell range B29:C33.
• Secondly, from Formulas tab >>> Create from Selection.

A dialog box will appear.

• Then, select Top row.
• Finally, press OK.

At the top right corner, there is a Name Box. We can click it there, to see our Named Range.

Upon clicking, we can see our two Named Ranges: Beverages and Snacks.

• Now, select cell B4.
• Then, bring up the Data Validation dialog box from the Data
• After that, pick these settings:
  • Allow: List.
  • Source: select cell range B29:C29.
• Finally, press OK.

Now, we can see, the B5 cell is showing the two categories. Now, the next step is to make a dependent list on the C5 cell.

• Firstly, select the cell range C5:C11.
• Secondly, from the Data tab bring up the Data Validation dialog box.
• Thirdly, set these values:
  • Allow: List.
  • Source: Type the following formula there.

• Finally, press OK.

We can see that, in cell C5, products from the Snacks category are showing.

Further, if we change cell B5 to Beverages, then cell C5 will change too.

In conclusion, we can do this for the rest of the cells and fill them with dependent values.

Read More: How to Use Data Validation in Excel with Color (4 Ways)

Practice Section

We’ve provided a practice dataset for each section in our Excel file. You can try those to master these 6 techniques.

Conclusion

Our article has shown you 6 examples of how to use the Data Validation list from another sheet. If you face any problems, feel free to comment below for clarification. Thanks for reading, keep excelling!

Related Articles

• How to Use IF Statement in Data Validation Formula in Excel (6 Ways)
• Excel VBA to Create Data Validation List from Array
• How to Use Custom VLOOKUP Formula in Excel Data Validation
• [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
• Default Value in Data Validation List with Excel VBA (Macro and UserForm)