Lỗi cannot create links to consolidation sheet
This tutorial is a detailed step-by-step guide to data consolidation in Excel. The Data Consolidation tool combines or consolidates data in different worksheets or workbooks in Excel. Also read:
How to Pull Geographical Data (County/City) in Excel? Data consolidation in Excel enables a user to consolidate data from a separate worksheet to a master worksheet or, from a different workbook to a master workbook. Essentially, it grabs data from a series of sheets or even workbooks and brings it together in a
single worksheet in Excel. However, this feature can sometimes play off tricks if you aren’t quite aware of its usage. Well, do not be overwhelmed. It shouldn’t be misunderstood to be a faulty feature. To avoid some of the common errors that occur during the data consolidation process, we discussed resolving those, towards the end of the guide. Let’s get started
with this detailed step-by-step guide to consolidating data from one or more sheets or files in Excel. Consolidated data can be combined by adding, multiplying, averaging, counting, etc. in a single sheet. To consolidate data from multiple sheets in Excel, do as follows next. First, we will prepare datasets in three different worksheets in a file like this. 2017 Data Sheet2018 Data Sheet2019 Data Sheet
Pro Tip: To name your selections or references before going ahead with the consolidation process, place your cursor on the Reference field in the consolidation window, press F3 or Fn+F3 and select a reference under All References. Let us move on to consolidating the selected datasets.
You will now see the consolidated data showing up in the sheet. Here, we have opted to sum all the values. If you opt to count or multiply the values, you will see different results. Here’s how the consolidated data looks with a few stylings. Consolidated DataResolving error: Source reference overlaps destination areaAt this point, make sure the cell ranges of each table are different. The ranges must not overlap each other, which is what results in this error. If the cell range of a table in the first sheet is A1:E6, make sure the tables in the other sheets have been placed on different ranges and they do not overlap as well. Resolving error: Cannot create links to consolidation sheetYou must have faced this error when you tried to consolidate data and checked the Create link to source option in the dialogue box. However, this option must not be selected before consolidating data. This is when you see this error. It gets a bit tricky here when you want to enable an auto-update feature for the consolidated data. Follow the steps below to avoid this error. To auto-update the consolidated data table on changes, select the consolidated data table, click Consolidate in the Data tab. In the Consolidate window, under the Use Labels in section, check the Create link to source data option. Consolidate data from multiple workbooks to a new workbookTo consolidate data from multiple workbooks to a new workbook, follow the steps below. Before we start, make sure all workbooks that you wish to consolidate are open.
Repeat the step of selecting the tables from every workbook in the same way as we did in the first example. The only step that is different here is that we’re selecting references from different workbooks using the Browse button, beside the Reference field in the Consolidate dialogue box. ConclusionThis tutorial walks you through a detailed step-by-step guide on Data Consolidation in Excel. This piece explains two ways to consolidate data i.e., consolidating data from multiple sheets to a new sheet. Second, consolidating data from multiple workbooks to a new workbook. Reference: Happy. |