Get data from another sheet Excel

Excel is extremely powerful even using just the basic functionality of adding data to cells and spreadsheets, sorting and cultivating that data into a beautiful work of cellular delight. Yet Excel is capable of far more than the standard editing of cell contents allows, through the magic of Visual Basic for Applications scripts, or VBA.

We’ll briefly explore one simple example of using VBA in Excel to extract values from one worksheet, but this is just the tip of the iceberg for what Excel and VBA can accomplish together.

Accessing the Visual Basic Editor

To begin, you’ll need to locate the Visual Basic Editor in Excel. Finding this will depend on what version of Excel you are running, but for most modern versions, the Visual Basic Editor can be found under the Developer tab of the menu ribbon.

Once located, simply click Macros, enter a macro name (we’ll use MySum for this example), then click create to open the editor and begin scripting.

The Script

Once inside the editor you’ll see the declaration of your MySum macro, which is just a standard vba function that we can edit to do our bidding.

For our example, perhaps we have a worksheet named Sales and we want to use this macro to total (sum) the first 25 values in column B.

Thus to begin our script, we need to select the appropriate worksheet named Sales, and then using that selected object, narrow our selection by grabbing the specific cells (known as a range of cells) that we want to total.

Sub MySum()
    Worksheets("Sales").Range("B1:B25")
End Sub

Now we actually need to get the total or sum of all those values, so we wrap the previous object call in a WorksheetFunction of Sum, like so:

Sub MySum()
    WorksheetFunction.Sum(Worksheets("Sales").Range("B1:B25"))
End Sub

Yet that’s not the final step. In our example, we want to extract this sum total from the Sales worksheet and then insert that total value into the current cell we have selected when running this macro.

To accomplish this, we’ll use the ActiveCell object, setting its Value to be equal to our summed total:

Sub MySum()
    ActiveCell.Value = WorksheetFunction.Sum(Worksheets("Sales").Range("B1:B25"))
End Sub

Alright, now our macro is complete, so let’s save and go back to our actual spreadsheet to test it out.

Running the Macro

To ensure it is functional, select a different spreadsheet, then select a single cell where you want the sum to be inserted.

To run a saved macro, just go back to the Developer tab, select Macros again, and choose your MySum macro. The cell contents should now be replaced with the sum total of the Sales column B values we specified.

Excel can easily pull data from another sheet based on different criteria by using different functions. We don’t need to type data again and again for different sheets. Today we are going to know about another use of these functions of Excel.

Download Practice Workbook

Download the following workbook and exercise.

4 Ways to Pull Data From Another Sheet Based on Criteria in Excel

1. Use of Advanced Filter to Pull Data From Another Sheet

Advanced Filter is one of the most common and easiest ways to pull data from another sheet based on criteria. Let’s consider, we have a dataset of the customer and their payment history. In the next spreadsheet, we are going to pull out the details of the customers who paid through Card.

STEPS:

• In the second spreadsheet, go to the Data option from the ribbon.
• Select Advanced from the Sort & Filter group of commands.

• Now in the dialogue box, select ‘Copy to another location.
• Select the List range from the source sheet.

• Then click on the Criteria range and put data based on the criteria we want.

• After that, select the cell where we want to copy the extracted data and press OK.

• Finally, we can see the extracted data and use them for further purposes.

Read More: How to Pull Data from Multiple Worksheets in Excel VBA

2. Use of VLOOKUP Formula in Excel to Get Data From Another Sheet

VLOOKUP means Verticle Lookup. To search for certain data in a column, we use VLOOKUP Function. Here is a dataset of the customers.

We are going to input the missing data from another spreadsheet ‘Sheet2’.

STEPS:

• Select Cell E5.
• Type the formula:

=VLOOKUP(C5,Sheet2!B5:C8,2,0)

➧ NOTE: Here first of all we put the lookup value that we wanted to search in the next sheet. Then select the sheet range from the next sheet. Also, input the column number in which we want to pull out the data. Finally, for the exact match, we write 0.

• Now hit Enter.
• After that drag down the formula through the column.
• At last, we can see the result.

Read More: Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP

Similar Readings

• How to Import Text File with Multiple Delimiters into Excel (3 Methods)
• Import Data from Text File into Excel (3 Methods)
• How to Import Data from Secure Website to Excel (With Quick Steps)
• Convert Excel to Text File with Pipe Delimiter (2 Ways)
• How to Convert Notepad to Excel with Columns (5 Methods)

3. Combine INDEX & MATCH Functions to Obtain Data From Another

INDEX & MATCH Functions combo is a popular and powerful tool in Microsoft Excel to return the value from a particular portion of the list. Using this combo, we can pull data from another sheet based on criteria. Assuming we have a customer dataset with their payment information.

Here on another sheet ‘Sheet3’, we are going to pull out the Amount values of the customers.

STEPS:

• At first, select Cell D5.
• Then type the formula:

=INDEX('INDEX & MATCH Functions'!B5:E5,MATCH($B$5,'INDEX & MATCH Functions'!$B$4:$E$4,0))

➧ NOTE: Here the MATCH Function finds the exact match of a value from the array of another sheet. The INDEX Function returns that value from the list.

• Press Enter and drag down the cursor to see the rest of the result.
• Finally, it’s done.

Read More:How to Extract Data from a List Using Excel Formula (5 Methods)

4. Use of HLOOKUP Function to Pull Data From Another Sheet Based on Criteria in Excel 

The HLOOKUP Function does the horizontal lookup to bring back the value from the data. Let’s say we have a spreadsheet of customers’ payment history.

We are going to pull out the data into another spreadsheet ‘Sheet4’. We can see a helping column which needs for the calculations.

STEPS:

• Select the Cell E5.
• Write down the formula:

=HLOOKUP($B$5,'HLOOKUP Function'!$B$4:$E$8,Sheet4!D5+1,0)

• Hit Enter and drag down the cursor to the cells below for the result.

Read More: Excel VBA: Pull Data Automatically from a Website (2 Methods)

Conclusion

By following these ways, we can easily get data from another sheet based on criteria in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.

Related Articles

• How to Extract Data From Table Based on Multiple Criteria in Excel
• Import Data from Excel into Word Automatically Using VBA (2 Ways)
• Excel Macro: Extract Data from Multiple Excel Files (4 Methods)
• How to Extract Data from Excel to Word (4 Ways)
• How to Extract Year from Date in Excel (3 Ways)
• Extract Text After a Character in Excel (6 Ways)