How to create a dropdown list from another sheet in Excel
By
SpreadCheaters
In this tutorial we’ll learn how to create a dropdown list from another sheet in Excel. Let’s see the dataset above, it has records of payments made against order IDs. We’ll create a drop-down list to update the current status of payment and then decide whether the task is completed or still pending by using a simple IF function.
Let’s do it by following the steps mentioned below.
Drop down lists in Excel can be a useful tool for limiting the options available to users and ensuring that only valid entries are made in a cell or range of cells. This can be especially helpful in large or complex spreadsheets where it is important to maintain consistency and accuracy in the data. Drop down lists can also make it easier for users to enter data, as they only need to select from a predefined list rather than manually typing in a value. In addition, drop down lists can help to reduce errors and improve the overall organization and clarity of the spreadsheet.
Step 1 – Create the list items on another sheet
– Text dropdowns in Excel can be created by using the data from a list of data in Excel. So, we’ll create this list on another sheet by writing “Paid”, “Partially Paid” and “In Process” in three cells on another sheet as shown above.
Step 2 – Create the dropdown list by choosing the list items as source
– Select all the cells where you want to create the dropdown list.
– From the list of main tabs, click on the Data tab.
– Now in Data Tools locate the Data Validation action button and press it.
– This will open up the Data Validation dialog box. In the validation criteria choose a list.
– Click on source and then select the cells on the source sheet in which we entered the list items in the last step. Press the OK button.
– This will create a dropdown list in the selected cells as shown below.
– Now we can select any value from this dropdown created from source data in another sheet.
Step 3 – Use IF function to create a meaningful status
– Now we’ll use a simple condition with IF function to create a meaningful final status.
– We’ll use the following formula;
=IF(C2=”Paid”, “Complete”, “Pending”)
This will check the payment status and if the payment status is Paid then we’ll mark the task as “Completed” otherwise the status will be marked as “Pending” as shown above.
