Skip to main content

Quickly fill cell references from multiple worksheets in Excel

Author: Xiaoyang Last Modified: 2020-04-10

Kutools for Excel

Boosts Excel With 300+
Powerful Features

Sometimes, you may need to reference a specific cell in all of the worksheets and display the results in separate cells on a summary sheet. For example, you have a workbook which contains multiple worksheets named Sun, Mon, Tue… Sat, in each sheet, the cell B7 has the total price for each day. On your summary sheet you want these individual total price cells B7 to be listed on separate rows. You can point the reference manually to each cell of each sheet. But if there are lots of sheets, this way will be tedious.

However, if you have Dynamically Refer to Worksheets of Kutools for Excel, you can quickly create and fill same cell references from multiple worksheets into one worksheet.


Click Kutools >> More >> Dynamically Refer to Worksheets, see screenshots:

kutools-arrow-right
shot-fill-cell-refs2

Usage:

Supposing you have a workbook, and want to extract the total price (the total price is placed in B7) from each worksheet (all worksheets have the same data structure) into the master worksheet, and finally get the result as the following screenshots shown. Please do as follows.

-2

1. Go to the Master worksheet, click on a cell which represents the reference you will extract across other sheets, in this case, I click cell B7.

2. Then apply this utility by clicking Kutools > More > Dynamically Refer to Worksheets.

3. In the Fill Worksheets References dialog box, please configure as follows.

  • Choose Fill vertically cell after cell from the Fill order drop-down list;
  • Click the little lock icon beside the Fill formula box. After clicking, you can see the grey Lock icon turns to yellow, which means the Fill formula is locked;
    Note: Now the cell reference you specified in step 1 is displayed in the Fill formula box.
  • Click a cell in this Master worksheet to output the first extracted value. In this example I click cell B2;
    Note: If you don’t lock the Fill formula in advance, it will be changed with this currently selected cell automatically.
  • Click the Fill Range button.

4. Then values in cell B7 of each worksheet have been listed vertically in the Master worksheet. Please close the Fill Worksheets References dialog box.

Tips:

If you want to display the cell references horizontally cell after cell, please choose Fill horizontally cell after cell from the Fill order in the dialog box.


Demo: Quickly fill cell references from multiple worksheets in Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 30 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments (6)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
this isnt working with tables, it doesn't display anything!
This comment was minimized by the moderator on the site
It wont work on the last 5 of sheets, even if i only select them. help?
This comment was minimized by the moderator on the site
I am suddenly limited to 15 sheets, is there a fix for this?
This comment was minimized by the moderator on the site
I need the same thing as Can. I have a list of cells in one woksheet that I want to populate same cell in each worksheet
This comment was minimized by the moderator on the site
How can I do the opposite of this. I mean filling cells in the seperate worksheets from a range from main worksheet
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations