Function Problems

How to Automatically Pull Data from Worksheet Tabs into an Excel Summary

Phi Hung Vo Oct 9, 2026 870 views

Question details

The user needs to retrieve specific data points such as ID, start date, and completion status from multiple uniformly formatted worksheet tabs into a single summary sheet.

How to Automatically Pull Data from Multiple Worksheets into an Excel Summary

Product: Excel
Device & OS: not provided
Scenario: Creating a summary worksheet that dynamically consolidates specific cell values from multiple assignment tabs using the same layout template.
Observed behavior: Data needs to be extracted from cells B1, B2, and F1 across multiple sheets dynamically, avoiding the need to manually link every cell.

Before you start

Ensure that all your source worksheet tabs follow the exact same template and that you have a list of exact sheet names ready to use in your summary sheet.

Solution 1Recommended

Use the INDIRECT Function to Extract Data Dynamically

The INDIRECT function converts text strings into cell references, allowing you to pull data from multiple sheets dynamically based on a list of sheet names.

By listing the names of your worksheets in your summary sheet, you can use INDIRECT to reference those names and extract data from specific cells across all tabs without writing a unique formula for each one.

List Worksheet Names

In your Summary sheet, enter the exact names of your assignment worksheets in a horizontal row, for example, in cells B2, C2, and D2.

Enter Formula for the First Data Point (ID)

Select cell B3 and enter the formula =INDIRECT("'"&B$2&"'!B1"). This formula takes the sheet name from B2 and retrieves the value from cell B1 of that specific sheet.

Enter Formula for the Second Data Point (Start Date)

In cell B4, enter the formula =INDIRECT("'"&B$2&"'!B2") to pull the start date stored in cell B2 of the respective assignment sheet.

Enter Formula for the Third Data Point (Completion Status)

In cell B5, enter the formula =INDIRECT("'"&B$2&"'!F1") to fetch the completion status from cell F1.

Apply Formulas to Other Columns

Highlight cells B3 through B5, click and hold the fill handle in the bottom-right corner of the selection, and drag it to the right across your summary sheet to automatically pull data for the remaining worksheets.

Use the INDIRECT Function to Extract Data Dynamically

Exact Match Required: Ensure that the worksheet names entered in row 2 match your actual tab names exactly, including any spaces and punctuation. The single quotes in the formula are there to ensure sheet names with spaces are processed correctly.

Efficient Data Management with WPS Office

Consolidate Worksheet Data Easily in WPS Spreadsheet

You can seamlessly use the INDIRECT function to pull data from multiple worksheet tabs into a summary sheet using WPS Spreadsheet. It offers full compatibility with Excel formulas and provides a fast, intuitive interface for heavy data management tasks.

1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your assignment tabs.
2. Create a Summary layout: Navigate to your Summary sheet and type out your exact tab names in the top row (e.g., Row 2).
3. Enter the INDIRECT formula: Click the target cell and type your INDIRECT formula, such as =INDIRECT("'"&B$2&"'!B1"), then press Enter.
4. Fill the remaining cells: Drag the fill handle across your table to instantly populate the rest of your summary data.

Fully compatible with Microsoft Excel formulas, including INDIRECT, VLOOKUP, and XLOOKUP.Lightweight and fast, ensuring smooth performance even when handling workbooks with dozens of tabs.Free to download with an intuitive, easy-to-navigate tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDIRECT formula returning a #REF! error?

A #REF! error typically occurs if the worksheet name referenced in the formula is misspelled, contains extra spaces, or if the referenced sheet has been deleted or renamed.

How do I handle spaces in worksheet names with the INDIRECT function?

To handle spaces, you must wrap the sheet name reference in single quotes within your formula string. For example, use =INDIRECT("'"&A1&"'!B1") where A1 contains the sheet name.

Can I use INDIRECT to pull data from a completely different workbook?

Yes, but the INDIRECT function only works with open workbooks. If the source workbook is closed, the formula will return a #REF! error. For pulling data from closed workbooks, Power Query is recommended.

Is there a faster way to list all my worksheet names in the summary sheet?

You can use an older Excel macro 4.0 function or VBA to generate a list of all sheet names dynamically, or simply copy and paste the names manually if the number of sheets is small.

Excel & Data Problems