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

Get FREE Advanced Excel Exercises with Solutions!

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


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

We’ve 2 columns in this dataset. Product purchased by a customer will be inserted using the Data Validation drop-down 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.

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 “source” sheet.

  • 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


2. Applying 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 “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 doesn’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


3. Fixing Time Limit by Applying Data Validation List from Another Excel 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.

Data Validation list from another Sheet

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


4. Getting Greater Number with Excel 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.


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


6. Using Data Validation List from Another Sheet to Create 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 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 tab.
  • After that, pick these settings:
    • Allow: List.
    • Source: select cell range B29:C29.
  • Finally, press OK.

Now, we can see, that 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: Excel Data Validation Drop Down List with Filter


Download Practice Workbook


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


<< Go Back to Data Validation in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo