Learn to pull data from another sheet based on criteria using built-in functions and macros in Excel, Google Sheets, and OpenOffice Calc.
In this article, we will learn the step-by-step process to pull data from another sheet in Excel and OpenOffice using Macros and the Advanced Filter Option. In Google Sheets, it uses the built-in FILTER Function.
Table of Contents
Key Takeaways
- Data can be dynamically pulled from another sheet in Excel, OpenOffice Calc, and Google Sheets using Advanced Filter, Macros, and the FILTER function.
- These methods help create automated reports and ledgers without manually copying large datasets, saving significant time.
- Excel uses Advanced Filter + VBA macros to automate data extraction based on criteria like customer or debtor name.
- OpenOffice Calc also uses Advanced Filter with macros, allowing users to copy filtered results to another sheet automatically.
- Google Sheets simplifies the process using the built-in FILTER function for real-time data extraction without macros.
Pull Data From Another Sheet Based On Criteria In Excel Using the 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 sample data below from 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, 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 the Advanced Filter Option In Excel
Firstly, click on an empty cell in the debtors sheet beside the criteria.

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

Define the macro name as per your choice. We have defined it here as “Aakash”. You need to write the name without capitalization. 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 are 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 image above, this fetched the data of Aakash Traders from the Accounts Receivable Ledger and transferred 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 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 “CallTransfer” 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:
- Start Recording the Macro. You can also use the shortcut key ALT + T + M + R.
- Select the source data.
- Go to Filter. Select Advanced Filter. You can also use the shortcut key ALT + D + F + A.
- Define Criteria and select the criteria cells.
- Click on ” More” and select ” Copy To”. Define the sheet or cell where you want to copy the data.
- Click “OK”.
- 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 for 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 the 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 the 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. The following dialog box appears.

Select Criteria. Our criteria here is “Aakash Traders”. Select cells 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 filtered data 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 advanced 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 Sheets 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 Sheets makes the work of transferring or pulling data from another sheet super easy. All you have to do is 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 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, or amount. You should keep 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 the Accounts Receivable Ledger “.
Frequently Asked Questions
What is the easiest way to pull data from another sheet in Google Sheets?
The easiest way is using the FILTER() function, which automatically extracts data based on conditions.
Do I need macros for Google Sheets data filtering?
No, Google Sheets does not require macros for this task as it has built-in functions like FILTER.
Why is Advanced Filter used in Excel?
Advanced Filter helps extract specific data sets based on criteria and can copy results to another sheet.
Can I automate data pulling in Excel without manually running filters every time?
Yes, by using VBA macros and assigning them to sheet events, the process can be fully automated.
What are common use cases for pulling data from another sheet?
It is commonly used for generating customer-wise reports, invoices, ledgers, and financial summaries.
If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from it. Sharing is Caring.
If you have any queries or suggestions, please share them in the comment section below. We will be more than happy to assist you.

