How To Pull Data From Another Sheet Based On Criteria?

Learn to pull data from another sheet based on criteria using inbuilt functions and macros in Excel, Google Sheet, and OpenOffice Calc.

Usually, this technique is used when a user wants to bifurcate a big set of data or prepare reports from a given set of data. Thus, manually extracting these data sets becomes hectic.

Excel, OpenOffice Calc, and Google Sheet consist of inbuilt functions that help you to simplify your hectic and time-consuming tasks.

In this article, we will learn the step-by-step process to pull data from another sheet in Excel and OpenOffice using Macros and Advanced Filter Option. In Google Sheets, it uses the inbuilt FILTER Function.

Pull Data From Another Sheet Based On Criteria In Excel Using Advanced Filter Function

This technique sorts the data based on given criteria and transfers the data to another sheet. You can use this option to transfer data in the same sheet or another sheet. For this option to work, the column headings must be similar to the source file.

Let us understand it with an example.

Example

We have taken the below sample data of a company’s Accounts Receivable Ledger.

As we can see there are two customers in the data. One is Aakash Traders and another one is Sundar Brothers Ltd. If we want to copy data of only Aakash Traders in another sheet, we need to use the Advanced Filter Option.

You can do this by following 5 easy steps. But before we begin, there are some things we need to do before using the Advanced Filter option.

1. Select the headings of the Accounts Receivable Ledger and activate “Filter” by going to the “Data” tab in the ribbon and clicking on the funnel-like Filter icon.

2. Copy the same headings in the sheet where you want to transfer the data after leaving 3 rows.

3. At the top, copy the criteria. In this example, we want to filter the data based on the “Debtors Name”.

Steps To Use Advanced Filter Option In Excel

Firstly, click on an empty cell in the debtor’s sheet beside the criteria.

Go to View Tab and click on “Macros”. Select “Record Macros”.

Define Macro name as per your choice. We have defined here as “Aakash”. You need to write the name without caps. Click “OK”.

Go To the “Data” tab in the ribbon and select “Advanced” in the “Sort and Filter” section. The following window will appear.

Select “Copy to another location”.  Press tab and move to “List Range”. Click into the box and navigate to the source file. In our case, it is Accounts Receivable Ledger. Select the complete data including the headings.

In “Criteria” select the criteria range that we earlier defined. In our case, it is cell B1:B2. Debtors Name and Aakash Traders is our criteria.

In the “Copy To” section, select the headings in the debtor’s sheet where you want to transfer the data (B4:H4) and click “OK”.

As you can see in the above image above, this fetched the data of Aakash Traders from the Accounts Receivable Ledger and transfer the data to the debtor’s sheet.

The user needs to run the macro every time they enter new data. Thus to automate this process, we need to add a VBA code to run the macro automatically whenever the user clicks on the sheet.

To do so follow the following instructions:

Right-click on the debtor’s sheet. Click on View Code. The following VBA window will open.

Under the first code line type “call transfer” or write call with your macro name. Click on the “Save” button and close the VBA window. Thus, this will automate the process every time you click on this sheet.

If you change the debtor’s name to Sundar Brothers Ltd and navigate to this sheet, the file displays the data of Sundar Brothers Ltd.

Pull Data From Another Sheet Based On Criteria In OpenOffice Calc Using Advanced Filter Function

Advanced Filter in OpenOffice Calc helps us to transfer data from one sheet to another. In this article, we will learn the step-by-step process to use Advanced Filter.

Follow these 7 easy steps given below:

1. Start Recording the Macro. You can also use the shortcut key ALT + T + M + R.
2. Select the source data.
3. Go to Filter. Select Advanced Filter. You can also use the shortcut key ALT + D + F + A.
4. Define Criteria and select the criteria cells.
5. Click on ” More” and select ” Copy To”. Define the sheet or cell where you want to copy the data.
6. Click “OK”.
7. Stop recording the “Macro” by clicking the “Stop Recording” pop-up window on your screen in the top left corner.

Let us understand the above process with an example.

Example

Click here to download the sample datasheet. There are 3 sheets in this workbook.

As you can see, there is data of two vendors/debtors in the sheet. Now, if we want to create a debtors Ledger, we have to manually copy the data every time a new invoice is issued.

Moreover, when the frequency of invoices is higher, this job is tiresome. To automate this process, we can take the help of macros and advanced filter options in OpenOffice Calc.

Let us apply the above-mentioned step-by-step process.

Steps To Use Advanced Filter Option In OpenOffice Calc

Run the macro using ALT + T + M + R to transfer the data from the Accounts Receivable Template sheet to Debtor’s Sheet. See the image below:

Click on any cell in Debtor’s Sheet. Now select the data in the source file. In our Accounts Receivable file, select B6:U53.

Press ALT + D + F + A to open the Advanced Filter Option. Following dialogue box appears.

Select Criteria. Our criteria here is “Aakash Traders”. Select cell B2:B3. Click on the “More” button in the right corner of the dialog box.

Check the “Copy results” option and select the cell where you want to paste the data. It should be kept in mind that the data filtered will also copy the headings from the source data. Thus, unlike excel there is no need to insert the same columns in the debtor’s sheet.

Now, press “OK” and click on the “Stop recording” button at the top left corner. This will execute the advance filter and transfer the data to the debtor’s ledger of Aakash Traders.

As soon as you make a new entry in the Accounts Receivable Ledger, you just have to run the macro using the following shortcut ALT + T + M + U and select the macro name as shown below:

Pull Data From Another Sheet Based On Criteria In Google Sheets Using The FILTER Function

Google Sheet consists of an in-built FILTER Function. This function works the same as the Advanced Filter Option in Excel and OpenOffice Calc.

The FILTER Function in Google Sheet makes the work of transferring or pulling data from another sheet super easy. All you have to do is to insert the function and define the parameters. That’s it and you are done.

Syntax of FILTER Function in Google Sheets

Syntax = FILTER(range, condition1, [condition2, …])

Using the above FILTER Function, insert the following formula:

=FILTER(‘Accounts Receivable Template’!B7:U61,‘Accounts Receivable Template’!E7:E61=B3)

Let us understand with an example.

Example

Given below is the Accounts Receivable Ledger.

The “range” is the “B7:U61 in Accounts Receivable Ledger”.

The “condition” is that “E7:E61 in Accounts Receivable Ledger “ is equal to “B3 in Debtor’s Ledger”. Now press “Enter”. See the image below:

This function fetches the data based on the given criteria. Our criteria here mentioned is “Aakash Traders”. Hence, the function fetches the data of “Aakash Traders” in the Debtor’s Sheet.

You can give any criteria based on your requirement. It can be a specific month, date, amount. It should’ve kept in mind that the range needs to be changed accordingly.

In our example, we wanted to filter the data according to the debtor’s name. Hence, we selected the range as “E7:E61 in Accounts Receivable Ledger “. 

If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from the same. Sharing is Caring.