Power Query Problems

How to Automatically Transfer Active Jobs Between Excel Workbooks

Maira Mehtab Oct 8, 2026 871 views

Question details

The user needs to automatically extract and transfer ongoing jobs from a master Excel workbook to a separate workbook, specifically excluding jobs that have completion or cancellation dates.

How to Automatically Transfer Active Jobs Between Excel Workbooks

Product: Excel
Device & OS: not provided
Scenario: Managing a master list of jobs and dynamically displaying only the active jobs in a different destination workbook without manual copying.
Observed behavior: The user wants to establish an automated connection that filters out rows where columns AD or AE contain date entries, displaying only the current active jobs upon refreshing.

Before you start

Ensure both your master jobs workbook and your destination workbook are saved in a stable, accessible location, and that your source data has clear column headers.

Solution 1Recommended

Use Power Query to Import and Filter Data

Power Query is the most efficient and reliable method to link workbooks and automatically filter out jobs based on specific date columns.

By converting your source data into an Excel Table and using Power Query, you can create a dynamic link between the two workbooks. This allows you to apply persistent filters that automatically remove completed or canceled jobs every time the data is refreshed.

Format the Source Data as a Table

Open your master workbook containing all jobs. Select the entire data range, press Ctrl+T on your keyboard to convert it into an Excel Table, and ensure 'My table has headers' is checked. Save and close this workbook.

Connect to the Master Workbook

Open your destination workbook where you want active jobs to appear. Go to the 'Data' tab on the ribbon, click 'Get Data' (or 'New Query'), select 'From File', and then choose 'From Workbook'. Locate and select your master workbook.

Load Data into Power Query

In the Navigator window that appears, select the table you just created in the master workbook, and click the 'Transform Data' button to open the Power Query Editor.

Filter Out Completed and Canceled Jobs

Scroll to column AD and column AE in the Power Query Editor. Click the filter dropdown arrow on each column's header, and select 'null' (or uncheck all specific date values) to exclude rows that have completion or cancellation dates.

Load and Refresh

Click 'Close & Load' in the top-left corner to bring the filtered active jobs into your new workbook. To update this list in the future, navigate to the 'Data' tab and click 'Refresh All'.

Use Power Query to Import and Filter Data

Automated Updates: Once this connection is established, you do not need to repeat these steps. Simply clicking 'Refresh All' will pull the latest active jobs from your master workbook.

Free Microsoft Office alternative

Manage Your Spreadsheets Seamlessly with WPS Office

If you frequently work with complex data filtering, job tracking, and interconnected workbooks, WPS Office offers a lightweight, highly compatible, and cost-effective alternative to Microsoft Excel.

1. Download and Install WPS Office: Visit the official WPS website to download the free installation package for your operating system.
2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx job tracking files directly without any formatting loss.
3. Filter and Manage Data: Use the familiar Data tab in WPS Office to apply advanced filters and manage your active jobs effortlessly.

Fully compatible with Microsoft Excel (.xlsx, .xls) file formatsFamiliar user interface with zero learning curve for Excel usersLightweight software that runs quickly, even with large datasetsFree built-in tools for advanced data filtering, sorting, and pivot tables

microsoft office alternative - wps office

Frequently Asked Questions

Why aren't my active jobs updating automatically when I open the destination workbook?

Data imported via Power Query does not update instantly on its own to save processing power. You must go to the 'Data' tab and click 'Refresh All' to pull the newest data from the master workbook.

Can I use standard formulas instead of Power Query to transfer the data?

Yes, you can use dynamic array formulas like the FILTER function across workbooks. However, both workbooks generally need to be open at the same time for standard formula links to update reliably, which makes Power Query the preferred solution for closed files.

What happens to my active jobs list if I rename or move the master workbook?

The Power Query connection will break because it relies on a specific file path. To fix it, open the destination workbook, go to 'Data' > 'Get Data' > 'Data Source Settings', and update the file path to point to the new location of your master workbook.

How does Power Query handle blank cells in the date columns?

In the Power Query Editor, blank cells in Excel are typically represented as 'null'. By filtering columns AD and AE to only show 'null', you are explicitly telling Excel to only import rows where no completion or cancellation date exists.

Excel & Data Problems