Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


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

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.

sample order details

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.

navigating to consolidate feature of Excel

  • 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.

The consolidate window of MS Excel

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

Pull Data for Same Cell from Multiple Sheets and Sum Them into a Master Column

  • 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

Pull Data for Same Cell from Multiple Sheets and Sum Them into a Master Column

  • 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.

Pull Data for Same Cell from Multiple Sheets and Sum Them into a Master Column

  • Drag the fill handle icon to copy the formula down.

copying an Excel formula using fill handle tool


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.

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

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

  • Now, drag the fill handle to copy the formula.

Note:

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

Read More: How to Extract Data from a List Using Excel Formula (5 Methods)


Similar Readings


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.

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

  • 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.

Read More: How to Pull Data from Multiple Worksheets in Excel VBA


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. And visit our site to find more such Excel-related content.


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo