Function Problems

How to Populate an Excel Table Data from Another Table Name

Partner Editor Oct 1, 2026 868 views

Question details

The user needs to retrieve and populate an entire table's data on a new worksheet by dynamically entering the source table's name in a designated cell.

How to Populate an Excel Table Data from Another Table Name

Product: Excel
Device & OS: not provided
Scenario: Managing multiple tables across different worksheets and needing to consolidate or dynamically view data by simply typing the target table's name.
Observed behavior: The user wants to input a source table name in one cell and have an adjacent empty area automatically fill with the corresponding table's columns and rows.

Before you start

Ensure that your source data is formatted as an official Excel Table (Insert > Table) and verify the exact Table Name by clicking anywhere inside the source table and checking the Table Name box in the Table Design tab.

Solution 1Recommended

Use the INDIRECT Function to Pull Table Data Dynamically

This method uses the INDIRECT function to convert a text string into a valid structural reference, allowing you to spill the entire table's contents into your destination sheet.

The INDIRECT function is designed to take a text string and resolve it into a valid cell or range reference. By combining a cell value with the structural reference '[#All]', Excel recognizes the text as a table name and retrieves both its headers and data dynamically.

Enter the table name

Type the exact name of the source table into a dedicated input cell on your destination sheet (for example, in cell A2).

Select the destination cell

Click on the cell where you want the top-left corner of the imported table to begin. Make sure there is plenty of empty space below and to the right.

Input the INDIRECT formula

Type the formula =INDIRECT(A2&"[#All]") into the destination cell and press Enter.

Verify the spilled array

The formula will automatically return the headers and data of the referenced table. Ensure no text or data blocks the spill range to avoid a #SPILL! error.

Use the INDIRECT Function to Pull Table Data Dynamically

Headers and Data Included: Using the '[#All]' tag ensures that both the table headers and the data rows are returned. If you only want the data without headers, simply use =INDIRECT(A2).

Easily Manage Dynamic Tables in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like INDIRECT and dynamic array spilling, making it simple to pull data across multiple sheets. It offers a seamless experience for managing complex data sets and is highly compatible with your existing files.

1. Format data as a Table: Open your workbook in WPS Spreadsheet, select your source data range, and press Ctrl+T to format it as a table.
2. Check the Table Name: Navigate to the Table Tools tab on the ribbon and note or rename the table in the Table Name box.
3. Input the reference name: In a new sheet, enter the exact table name you just verified into cell A2.
4. Apply the INDIRECT function: In an empty cell with enough surrounding space, type =INDIRECT(A2&"[#All]") and press Enter to instantly pull the table data.

Seamless compatibility with Microsoft Excel (.xlsx) formatsFull support for the INDIRECT function and dynamic array spillingFree, lightweight, and easy-to-use interface for managing large datasetsBuilt-in tools for formatting and analyzing tables quickly

Frequently Asked Questions

Why am I getting a #REF! error when using the INDIRECT formula?

A #REF! error usually occurs if the text in your reference cell (e.g., A2) does not match a valid table name in the workbook, or if you misspelled the structural tag. Double-check the exact spelling in the Table Design tab.

What does the #SPILL! error mean in this context?

A #SPILL! error means there is not enough empty space on your worksheet for the formula to return all the table's rows and columns. Clear any text, formulas, or formatting blocking the spill range to fix it.

How can I return only the table data without the headers?

To return only the data rows and exclude the header row, omit the '[#All]' part of the formula. Simply use =INDIRECT(A2) if cell A2 contains the valid table name.

Does this formula automatically update if I add new rows to the source table?

Yes. Because official Excel tables automatically expand to include newly added rows and columns, the dynamic array formula will automatically update and spill the new data into your destination sheet.

Excel & Data Problems