How to Move Data from One Sheet to Another Based on a Dropdown Selection in Excel Formula

In this article, we will learn how to move data from one sheet to another based on a dropdown selection in Excel. This is a useful technique when you have a large dataset in one sheet and you want to filter and display some specific data in another sheet. For example, you may have a sheet with sales data of different months and you want to show the sales of a selected month in another sheet.

To achieve this task, we will use the following steps:

• Create a dropdown list with the options you want to filter by. You can use the Data Validation feature in Excel to create a dropdown list from a range of cells or a predefined list of values.
• Use a lookup function to pull data from the source sheet based on the dropdown selection. You can use the VLOOKUP, HLOOKUP, INDEX, or MATCH functions to look up a value in a table and return a corresponding value from another column or row. You can also use the INDIRECT function to dynamically refer to a sheet name based on the dropdown selection.
• Copy the data to the destination sheet using a macro or a formula. You can use the Worksheet_Change event to trigger a macro that copies the data to another sheet when the dropdown selection changes. You can also use a formula that references the lookup function in the source sheet and copies the data to the destination sheet.

Procedures

To illustrate the procedures, we will use a sample dataset that contains the sales of three different months (January, February, and March) of some sellers in three different sheets. We will create a dropdown list in another sheet that allows us to select a month and display the sales of that month in the same sheet.

Step 1: Create the Dropdown List

First, we need to create a dropdown list with the month names in the destination sheet. To do this, follow these steps:

• Select a cell where you want to insert the dropdown list. In our example, we will select cell E3.
• Go to the Data tab and click on Data Validation in the Data Tools group.
• In the Data Validation dialog box, select List from the Allow drop-down menu.
• In the Source box, enter the month names separated by commas, or select a range of cells that contains the month names. In our example, we will enter Jan,Feb,Mar.
• Click OK to create the dropdown list.

Step 2: Use a Lookup Function to Pull Data from the Source Sheet

Next, we need to use a lookup function to pull data from the source sheet based on the dropdown selection. In our example, we will use the VLOOKUP function to look up the seller name in the leftmost column of the source sheet and return the sales amount from the second column. We will also use the INDIRECT function to dynamically refer to the sheet name based on the dropdown selection. To do this, follow these steps:

• Select a cell where you want to display the sales amount. In our example, we will select cell C5.
• Enter the following formula: =VLOOKUP($B5,INDIRECT("'"&$E$3&"'!$B$5:$C$11"),2,FALSE)
• Press Enter to display the sales amount of the selected month for the first seller.
• Drag the formula down to fill the other cells in the column.

The formula works as follows:

• The VLOOKUP function looks for the seller name in cell B5 in the leftmost column of the table specified by the second argument and returns the value from the second column of the same row.
• The INDIRECT function returns the reference specified by the text string in the first argument. The text string is composed of the sheet name in cell E3 enclosed by single quotes and an exclamation mark, followed by the range of cells that contains the sales data in the source sheet. For example, if the dropdown selection is Jan, the INDIRECT function returns the reference ‘Jan’!$B$5:$C$11.
• The third argument of the VLOOKUP function is 2, which means we want to return the value from the second column of the table.
• The fourth argument of the VLOOKUP function is FALSE, which means we want to find an exact match for the seller name.

Step 3: Copy the Data to the Destination Sheet

Finally, we need to copy the data to the destination sheet using a macro or a formula. In our example, we will use a formula that references the VLOOKUP function in the source sheet and copies the data to the destination sheet. To do this, follow these steps:

• Select a cell where you want to copy the data. In our example, we will select cell G5.
• Enter the following formula: =C5
• Press Enter to copy the sales amount of the selected month for the first seller.
• Drag the formula down to fill the other cells in the column.

Alternatively, you can use a macro that copies the data to the destination sheet when the dropdown selection changes. To do this, follow these steps:

• Right-click on the sheet tab of the destination sheet and select View Code from the context menu.
• In the Visual Basic Editor, enter the following code in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Check if the dropdown selection changes
    If Target.Address = "$E$3" Then
        'Define the source and destination sheets
        Dim sourceSheet As Worksheet
        Dim destinationSheet As Worksheet
        Set sourceSheet = ThisWorkbook.Worksheets(Target.Value)
        Set destinationSheet = ThisWorkbook.Worksheets("Destination")
        'Define the source and destination ranges
        Dim sourceRange As Range
        Dim destinationCell As Range
        Set sourceRange = sourceSheet.Range("B5:C11")
        Set destinationCell = destinationSheet.Range("G5")
        'Copy the data to the destination sheet
        sourceRange.Copy destinationCell
    End If
End Sub

• Save and close the Visual Basic Editor.

The code works as follows:

• The Worksheet_Change event is triggered when a cell value changes in the worksheet.
• The Target argument refers to the cell that changed.
• The If statement checks if the cell that changed is the dropdown cell (E3).
• The sourceSheet variable is set to the worksheet that has the same name as the dropdown value.
• The destinationSheet variable is set to the worksheet named “Destination”.
• The sourceRange variable is set to the range of cells that contains the sales data in the source sheet.
• The destinationCell variable is set to the cell where we want to paste the data in the destination sheet.
• The Copy method copies the data from the source range to the destination cell.