Pull Same Cell from Multiple Sheets into Master Column in Excel

In this article, I will show you how to pull the same cell from multiple sheets into a master column in Excel.

Pull Same Cell from Multiple Sheets into Master Column in Excel: 2 Ways

Before starting, let’s discuss the sample dataset we are going to use here.

We have order details for the first 6 months of a year (January, February, March, April, May, and June) in 6 consecutive worksheets. We have named the sheets Jan, Feb, Mar, Apr, May & Jun.

The following image shows the data for January.

Now, we will create a master column in a separate sheet (you can name it a master sheet). Then we will pull data from the same cell from multiple sheets.

Depending on the situation, you may need to just extract data, or you may need to not only extract data from the same cell of multiple worksheets but also add them up. So, we will discuss these two cases.

1. Pull Data of Same Cell from Multiple Sheets and Sum Them into a Master Column

In the 1st example, we will see how to pull data for the same cell from multiple worksheets and sum them into a master column in another sheet. We will use 4 methods to perform this: using the Consolidate feature, using cell reference, a formula using 3D reference, and using a formula with SUM, INDIRECT, and CELL functions.

i. Use of Consolidate Feature

First off, create a table like the following. We will keep all the cells empty so far.

Now, click on cell B5 and go to the Data tab → Data Tools group → click on Consolidate option.

The Consolidate window will be there. Now choose Function as Sum → Mark Left column in Use labels in section.
Now proceed to create a reference. Click the arrow icon as shown in the following image in the 3rd step.

Go to the first sheet Jan → select the range B5:C12 → again click on the arrow in Consolidate – Reference: window.

Now, we will be back to the Consolidate window.
This time, click on the Add button.

After pressing Add button, click on the next sheet, i.e. Feb.
Then again click on the Add button.

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

By now, you will discover that you are done with the task. See the image below of what we have got in our case.

ii. Using Cell Reference

Well, this is a less complex but tedious approach.

You just have to create a suitable table with a master column and write the following formula in the master column. Here our first cell is C5.
The formula is:

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

You can directly copy it and use it, or put an equal sign first → then go to the first sheet → click on the data cell → then put the plus sign → then go to the next sheet, and repeat all these until you reach the last sheet.
Then press ENTER. You will get the result.

Tiresome approach and we do not recommend this for workbooks with a huge number of sheets.

iii. Using Formula with 3D Reference

This approach is easier than using merely cell references and adding them up. Look how we use this here.

Keep all the source sheets one after another serially.
Then, go to the Qty column and type =SUM(, i.e. type an equal sign, write SUM, then type a parenthesis.
Then go to the first sheet, i.e. Jan.

Now, press the SHIFT key, keep a hold on it and go to the last sheet directly (you can ignore the sheets between them).
Click on the last sheet name.
Now, click on cell C5 (Choose the cell according to your dataset).
Finally, close the first brackets and press ENTER.

Drag the fill handle icon to copy the formula down.

iv. Using Formula with SUM, INDIRECT & CELL Functions

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

First of all, we have to 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, but we will not explain that here right now. That will be out of context. You can use the code from the file we have attached here. By the way, Excel has a new update, and the macro will be disabled in a downloaded XLSM file. You have to add our site to the trusted list in the internet options of your computer to enable the macros in it.
However, after creating the vertical list, enter the following formula in your master column.

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

Now, drag the fill handle to copy the formula.

Note:

Use absolute reference for the range that has the sheet name, here G5:G10.

2. Combine INDIRECT and CELL Functions to Pull the Same Cell from Multiple Sheets into Master Column

In the 2nd example, we will use the INDIRECT and CELL functions to pull the same cell from multiple sheets into a master column of a separate sheet.

I will not only give you the formula, but I will explain it too so that you can use it for your case.

First, let’s know what the INDIRECT and CELL functions do.
The INDIRECT function returns the reference specified by a text string.
On the other hand, the CELL function returns information about the location (and formatting+contents), according to the sheet’s reading order, in a reference.
Now, we have created 6 master columns for 6 months, and we want to extract the order quantity of each customer. These quantities are now located in 6 different sheets along with the customer names. We just want them together now.
First, we have to create a list of all sheet names serially in a horizontal manner.
We have created a list using a suitable VBA code and have added a button so that you can get the list of sheet names easily. Open the Excel file, and you will see it. To make the discussion shorter, I am not going to elaborate on those steps.

Now, write the following formula in cell C5 and drag it to right and down directions.

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

Now, look at your dataset. We have pulled order details from the same cells into separate columns.

🔎 Formula Breakdown:

K$5&”!”&CELL(“address”,$C5)

Output: “Jan!$C$5”
Explanation:
>> CELL(“address”,$C5), this part returns the address of the cell referenced with $C5. The output is $C$5. Note that, we have used $C5 as a reference. We have used the dollar sign before the column number because we will drag the formula to the right, but we don’t want to change the column reference.
Now, why have we used C5 as a reference?- It’s 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 right on it. So we have used $ before the row number, since we don’t want to change the row number but the columns.

>> The &, ampersand symbol will join 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”)

INDIRECT(“Jan!$C$5”)

Output: 1000
Explanation:
Now the INDIRECT function simply extracts the referenced value from cell C5 of the Jan sheet.

Download Practice Workbook

You can download the following practice workbook to practice along with it while reading this article.

Pulling Same Cell from Sheets into a Master Column.xlsm

Conclusion

In this article, we have shown 2 cases to pull the same cell from multiple sheets into the master column in Excel. If you have any queries regarding them, please leave us a comment.

<< Go Back To Extract Data Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!