VBA & Macro Problems

How to Combine Text Responses from Multiple Worksheets in Excel

Tauseeq MagsiTauseeq Magsi Oct 19, 2026 869 views

Question details

The user needs to consolidate survey or questionnaire responses spread across multiple identical worksheets into a single master summary sheet, organizing the data by stakeholder names.

How to Combine Text Responses from Multiple Worksheets in Excel
Product
Excel
Device & OS
not provided
Scenario
Consolidating data from identical questionnaires across multiple tabs into a master summary sheet where questions are listed as rows and stakeholder names are formatted as column headers.
Observed behavior
The goal is to automatically extract column D responses for column C questions from each worksheet, dynamically mapping them to the correct stakeholder (stored in cell C3) on a new summary sheet.
Before you start

Ensure all your questionnaire worksheets share the exact same layout (with questions in column C and responses in column D) and that your stakeholder names are consistently located in cell C3 across all tabs.

Solution 1Recommended

Use INDIRECT and Named Ranges to Consolidate Responses

Extract sheet names using an Excel 4.0 macro named range, then use the INDIRECT function to dynamically pull responses from each sheet into your master summary.

Since this method relies on an Excel 4.0 macro function to list sheet names dynamically, you must save your file as a Macro-Enabled Workbook (.xlsm) for it to function correctly.

1
Define a Named Range for Sheet Names

Navigate to Formulas > Name Manager > New. Create a name such as 'SheetNames' and enter the formula '=GET.WORKBOOK(1)' in the 'Refers to' field to extract all tab names from the workbook.

2
Set Up the Summary Sheet Rows

Create a new worksheet to serve as your summary sheet. List all your standardized questions down a single column (e.g., Column A), matching the exact layout of the original questionnaire rows.

3
List Stakeholder Names as Column Headers

Across the top row of your summary sheet, input the stakeholder names extracted from the 'SheetNames' named range so they act as your column headers.

4
Apply the INDIRECT Function

In the first response cell, enter an INDIRECT formula that references the column header (the worksheet name) and the specific cell containing the response on that sheet (e.g., =INDIRECT("'" & B$1 & "'!D4")). Drag this formula across your rows and columns to fill the data.

5
Save as a Macro-Enabled File

Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)'. Ensure macros are enabled in your Trust Center settings when reopening the file.

Use INDIRECT and Named Ranges to Consolidate Responses
Macro Requirement: The GET.WORKBOOK function is a legacy Excel 4.0 macro. It will not execute unless the workbook is saved with the .xlsm extension and macros are permitted to run on your device.
Advanced Data Consolidation

Easily Combine Worksheet Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced formulas like INDIRECT and legacy macro functions, making it simple to consolidate data across multiple worksheets. It provides a familiar, robust interface to streamline your data summarization tasks without breaking a sweat.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your multiple questionnaire tabs.
  2. 2. Create a Named Range: Navigate to the Formulas tab and click Name Manager to define your sheet names using the GET.WORKBOOK(1) formula.
  3. 3. Consolidate the data: Create your master summary sheet and use the INDIRECT function to dynamically pull the stakeholder responses based on the column headers.
  4. 4. Save as XLSM: Go to Menu > Save As and select the .xlsm format to ensure your macro functionality is preserved.
Fully compatible with Microsoft Excel formulas and macro-enabled files (.xlsm)Lightweight and fast, even when processing dozens of questionnaire worksheetsIntuitive Name Manager interface for easy named range setupFree to use for your everyday advanced data management needs
microsoft office alternative - wps office

Frequently Asked Questions

Can I combine worksheets without using macros?

Yes, but you will need to manually type the exact sheet names as column headers in your summary sheet instead of using GET.WORKBOOK. Once the sheet names are typed out, you can still use the INDIRECT function to pull the data dynamically.

Why does my INDIRECT formula return a #REF! error?

A #REF! error usually occurs if the worksheet name referenced in the formula does not exist, is misspelled, or contains spaces without being properly enclosed in single quotes within your formula (e.g., 'Sheet Name'!D4).

Do the stakeholder names have to match the sheet names exactly?

Yes. For the INDIRECT formula to correctly locate and extract data from the respective tabs, the stakeholder names used as column headers in your summary sheet must precisely match the actual worksheet tab names.