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.

Data Validation list from another Sheet


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 “dropdownsheet.
  • Then from Data tab >>> Data Validation.

After that, the Data Validation dialog box will appear.

Data Validation list from another Sheet

  • 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 “sourcesheet.

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

Data Validation list from another Sheet

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.

Data Validation list from another Sheet

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.

Data Validation list from another Sheet

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:

Data Validation list from another Sheet

  • Then pick the cell reference from the “sourcesheet.
    • Start date: B14 cell from “sourcesheet.
    • End date: B15 cell from “sourcesheet.

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.

Data Validation list from another Sheet

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.

Data Validation list from another Sheet

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.

Data Validation list from another Sheet

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 “sourcesheet.
    • End time: cell F15 from the “sourcesheet.
  • Finally, press OK.

Data Validation list from another Sheet

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:


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.

Data Validation list from another Sheet

Steps:

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.

Data Validation list from another Sheet

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.

Data Validation list from another Sheet

Steps:

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

Data Validation list from another Sheet

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.

Data Validation list from another Sheet

  • 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.

Data Validation list from another Sheet

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.

Data Validation list from another Sheet

  • 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.

Data Validation list from another Sheet

  • 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.
=INDIRECT(B5)
  • Finally, press OK.

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

Data Validation list from another Sheet

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.

Data Validation list from another Sheet

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

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo