Pull Same Cell from Multiple Sheets into Master Column in Excel

To demonstrate our methods, we’ll use the following sample dataset, which contains order details for the first 6 months of a year in 6 consecutive worksheets, named as Jan, Feb, Mar, Apr, May & Jun.

The following image shows the data for January.

sample order details

We will create a master column in a separate sheet (named Master Sheet), then pull data from the same cell from multiple sheets into it. Depending on the situation, we may need to just extract data, or to sum the extracted data. So, we will discuss these two cases.

Example 1 – Pull Data from the Same Cell in Multiple Sheets and Sum Them in a Master Column

We can use 4 different methods to perform this: using the Consolidate feature, a cell reference, a formula using 3D reference, and a formula with SUM, INDIRECT, and CELL functions.

Method i – Using the Consolidate Feature

In the ‘Master Sheet‘, create a table like the following. Keep all the cells empty to begin with.

Click on cell B5 and go to the Data tab → Data Tools group.
Click on Consolidate.

The Consolidate window will open.

Choose Sum as Function.
Select Left column in the Use labels in section.
Create a reference by clicking the arrow icon.

Go to the first sheet Jan.
Select the range B5:C12.
Again click on the arrow in the Consolidate – Reference: window.

We return to the Consolidate window.

Click on the Add button.

Click on the next sheet, i.e. Feb.
Click on the Add button.

Repeat the last two bullet points until the last sheet, Jun, is also added to the All references: list.
Click OK.

Our dataset is complete.

Method ii – Using Cell Reference

This is a less complex but more tedious approach.

Create a suitable table with a master column.
Enter the following formula in cell C5 in the master column:

=Jan!C5+Feb!C5+Mar!C5+Apr!C5+May!C5+Jun!C5

Go to the first sheet, Jan.
Click on the data cell.
Enter a plus sign.
Go to the next sheet, Feb, and click on the data cell.
Enter a plus sign.
Repeat the process for the rest of the sheets.
Press ENTER.

We do not recommend this method for workbooks with a huge number of sheets.

Method iii – Using Formula with 3D Reference

This approach is easier than merely using cell references and adding them up.

Ensure all the source sheets are listed one after another serially.
Go to the Qty column and in cell C5 enter =SUM(.
Go to the first sheet, i.e. Jan.

Press the SHIFT key, and while holding it down, click on the last sheet name.
Click on cell C5.
Close the parentheses on the formula and press ENTER.

Drag the Fill Handle icon down to copy the formula to the rest of the cells.

Method iv – Using Formula with SUM, INDIRECT & CELL Functions

Lastly, we can use a formula so that we don’t have to hover across all these sheets.

Make a vertical list of sheet names. In our case, the list is in G5:G10.
We have used a VBA code to create this list, but in order to stay focused we will not explain that here. Use the code from the practice workbook attached below.

Note – In the latest updates of Excel, the macro will be disabled in a downloaded XLSM file. You’ll have to add our site to the trusted list in the internet options of your computer to enable the macros in it.

After creating the vertical list, enter the following formula in your master column:

=SUM(INDIRECT($G$5:$G$10&"!"&CELL("address",C5)))

Drag the Fill Handle down to copy the formula to the rest of the cells.

Note:

Use absolute reference ($ signs before the values) for the range that has the sheet name, so G5:G10 becomes $G$5:$G$10.

Example 2 – Combining INDIRECT and CELL Functions to Pull the Same Cell from Multiple Sheets into Master Column

Here we will use the INDIRECT and CELL functions to pull values from the same cell in multiple sheets into a separate sheet, without summing the values.

The INDIRECT function returns the reference specified by a text string. The CELL function returns information about the location (and formatting and contents) in a reference, according to the sheet’s reading order.

Create a list of all sheet names serially and horizontally. In the attached Practice Workbook, we have created such a list using VBA code and have added a button so that you can get the list of sheet names easily.

Enter the following formula in cell C5:

=INDIRECT(K$5&"!"&CELL("address",$C5))

Drag the Fill Handle down and to the right to copy the formula to all the cells in the dataset.

We have pulled order details from the same cells into separate columns in our Master sheet.

Formula Breakdown:

K$5&”!”&CELL(“address”,$C5)
Output: “Jan!$C$5”
Explanation:
- CELL(“address”,$C5) returns the address of the cell referenced with $C5. The output is $C$5. The dollar sign before the column number allows us to drag the formula to the right without changing the column reference.
- We used C5 as a reference because all the source sheets have data (that we want to extract) in the C5:C12 range.
- Cell K5 has the name of the first sheet. The rest of the sheet names are to the right of it. So we used $ before the row number, since we don’t want to change the row number but the columns.
- The & (ampersand symbol) joins the sheet name, ! mark (needed to reference an Excel sheet) and cell reference within the INDIRECT function.

So the formula becomes:

INDIRECT(K$5&”!”&CELL(“address”,$C5)) → INDIRECT(“Jan!$C$5”),

which resolves to:

- INDIRECT(“Jan!$C$5”)
- Output: 1000
  Explanation:
  The INDIRECT function simply extracts the referenced value from cell C5 of the Jan sheet.

Download Practice Workbook

Pulling Same Cell from Sheets into a Master Column.xlsm

<< Go Back To Extract Data Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!