·
1 months ago
It wont work on the last 5 of sheets, even if i only select them. help?
Quickly fill cell references from multiple worksheets in Excel
Kutools for Excel
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.
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by
80%
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Click Kutools >> More >> Dynamically Refer to Worksheets, see screenshots:
 |
 |
 |
Usage:
Supposing you have a workbook which has several worksheets, and now you want to extract all the same cell references B7 from other worksheets into the master worksheet, get the result as the following screenshots shown:
 |
 |
 |
1. Go to your master worksheet and click the cell which you want to reference the same cells across other sheets, in this case, click cell B7.
2. Then apply this utility by clicking Kutools > More > Dynamically Refer to Worksheets.
3. In the Fill Worksheets References dialog box, choose Fill vertically cell after cell from the Fill order, and click the little lock beside the formula text box, and the grey lock will become yellow lock, that means the formula and cell reference has been locked, then you can click a cell to extract the cell B7 references, in this example I will click cell B1. (If you don’t lock this little lock, the cell reference and the formula you are created will be changed with the selected cell.) See screenshot:
4. Then click Fill Range, and the cell values in cell B7 of each worksheet have been listed vertically in the Master worksheet, and then close this dialog box. See screenshot:
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 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.
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 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.
Kutools for Excel
The functionality described above is just one of 300 powerful functions of Kutools for Excel.
Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment...
The comment will be refreshed after 00:00.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
· 8 months agoI am suddenly limited to 15 sheets, is there a fix for this? -
To post as a guest, your comment is unpublished.
· 1 years agoI 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
-
To post as a guest, your comment is unpublished.
· 1 years agoHow can I do the opposite of this. I mean filling cells in the seperate worksheets from a range from main worksheet
-
To post as a guest, your comment is unpublished.
· 1 years agoHi. Can you please look into making this tool work when the worksheets have "tables"?
Whenever I try to dynamically refer to cells that are within tables, the output cell contents are "0", unless I have selected the totals row from the table, at which point the output cell contents are blank (no content).