How to link two tables in Excel. Excel workbook. Communication between worksheets. Data sharing. Method – Linking cells using the Paste Special command

When performing certain tasks in Excel, sometimes you have to deal with several tables, which are also interconnected. That is, data from one table is pulled into others and when they change, the values in all associated table ranges are recalculated.

Linked tables are very convenient to use for processing large amounts of information. Having all the information in one table, moreover, if it is not homogeneous, is not very convenient. It is difficult to work with such objects and search for them. Linked tables, information between which is distributed but at the same time interrelated, are designed to eliminate this problem. Related table ranges can be located not only within one sheet or one workbook, but also located in separate workbooks (files). The last two options are used most often in practice, since the purpose of this technology is precisely to get away from the accumulation of data, and piling them on one page does not fundamentally solve the problem. Let's learn how to create and work with this type of data management.

First of all, let's look at the question of how it is possible to create a relationship between different table ranges.

Method 1: Directly linking tables with a formula

The easiest way to link data is to use formulas that reference other table ranges. It's called direct binding. This method is intuitive because it performs linking in almost the same way as creating links to data in a single table array.

Let's see how, using an example, a bond can be formed by direct bonding. We have two tables on two sheets. Calculation is performed on one table wages using a formula by multiplying the workers’ rate by a coefficient common to all.

The second sheet contains a table range containing a list of employees with their salaries. The list of employees in both cases is presented in the same order.

It is necessary to make sure that the data on rates from the second sheet is pulled into the corresponding cells of the first.

Method 2: using a combination of INDEX - SEARCH operators

But what to do if the list of employees in the table arrays is not in the same order? In this case, as discussed earlier, one option is to manually establish a link between each of those cells that need to be linked. But this is only suitable for small tables. For massive ranges, such an option, at best, will take a lot of time to implement, and at worst, in practice it will not be feasible at all. But decide this problem possible using a bunch of operators INDEX – SEARCH. Let's see how this can be done by linking data in tabular ranges, which were discussed in the previous method.

Select the first element of the column "Bid". Let's go to Function Wizard by clicking on the icon "Insert Function".

IN Function Wizard in Group "Links and Arrays" find and highlight the name "INDEX".

This operator has two forms: a form for working with arrays and a reference form. In our case, the first option is required, so in the next form selection window that opens, select it and click on the button "OK".

The operator arguments window is launched INDEX. Task specified function— output of the value located in the selected range in line with specified number. General operator formula INDEX is this:
INDEX(array, row_number, [column_number])

"Array"— an argument containing the address of the range from which we will extract information by the number of the specified line.

"Line number"- an argument that is the number of this very line. It is important to know that the line number should not be specified relative to the entire document, but only relative to the selected array.

"Column number"- an optional argument. We will not use it to solve our specific problem, and therefore there is no need to describe its essence separately.

Place the cursor in the field "Array". After this we go to Sheet 2 and, holding down the left mouse button, select the entire contents of the column "Bid".

After the coordinates are displayed in the operator window, place the cursor in the field "Line number". We will output this argument using the operator SEARCH. Therefore, we click on the triangle, which is located to the left of the function line. A list of recently used operators opens. If you find a name among them "SEARCH", then you can click on it. Otherwise, click on the very last item in the list - "Other functions...".

A standard window opens Function Wizards. Let's go to the same group "Links and Arrays". This time we select the item from the list "SEARCH". Click on the button "OK".

The operator arguments window is activated SEARCH. This function is designed to display the number of a value in a specific array by its name. It is thanks to this feature that we will calculate the line number certain value for function INDEX. Syntax SEARCH presented like this:
MATCH(lookup_value, lookup_array, [match_type])

"Search Value"— an argument containing the name or address of the cell in the third-party range in which it is located. It is the position of this item in the target range that should be calculated. In our case, the first argument will be cell references to Sheet 1, in which the names of employees are located.

"Browsing Array"— an argument that is a reference to an array in which the specified value is searched to determine its position. For us, this role will be played by the address of the column “ Name" on Sheet 2.

"Matching Type" is an optional argument, but, unlike the previous operator, we will need this optional argument. It specifies how the operator will match the search value to the array. This argument can have one of three meanings: -1 ; 0 ; 1 . For unordered arrays you should select the option «0» . Exactly this option suitable for our case.

So, let's start filling out the fields of the arguments window. Place the cursor in the field "Search Value", click on the first cell of the column "Name" on Sheet 1.

After the coordinates are displayed, place the cursor in the field "Browsing Array" and go to the shortcut "Sheet 2", which is located at the bottom of the Excel window above the status bar. Hold down the left mouse button and select all the cells of the column with the cursor "Name".

After their coordinates are displayed in the field "Browsing Array", go to the field "Matching Type" and use the keyboard to set the number there «0» . After this we return to the field again "Browsing Array". The fact is that we will copy the formula, as we did in the previous method. There will be a shift in addresses, but we need to fix the coordinates of the array being viewed. It shouldn't move. Select the coordinates with the cursor and press the function key F4. As you can see, a dollar sign has appeared in front of the coordinates, which means that the reference has turned from relative to absolute. Then click on the button "OK".

The result is displayed in the first cell of the column "Bid". But before we copy, we need to secure one more area, namely the first argument of the function INDEX. To do this, select the column element that contains the formula and move to the formula bar. Selecting the first argument of the operator INDEX (B2:B7) and click on the button F4. As you can see, a dollar sign appears near the selected coordinates. Click on the key Enter. In general, the formula took the following form:
INDEX(Sheet2!$B$2:$B$7,MATCH(Sheet1!A4,Sheet2!$A$2:$A$7,0))

You can now copy using the fill handle. We call it in the same way that we talked about earlier and stretch it to the end of the table range.

As we can see, despite the fact that the order of the rows of the two linked tables does not match, nevertheless, all values are adjusted according to the last names of the employees. This was achieved by using a combination of operators INDEX—SEARCH.

Creating Linked Tables

First of all, let's look at the question of how it is possible to create a relationship between different table ranges.

Method 1: Directly linking tables with a formula

Let's see how, using an example, a bond can be formed by direct bonding. We have two tables on two sheets. On one table, wages are calculated using a formula by multiplying the workers’ rate by a coefficient common to all.

The second sheet contains a table range containing a list of employees with their salaries. The list of employees in both cases is presented in the same order.

It is necessary to make sure that the data on rates from the second sheet is pulled into the corresponding cells of the first.

On the first sheet, select the first cell of the “Bet” column. We put a “=” sign in it. Next, click on the “Sheet 2” tab, which is located on the left side of the Excel interface above the status bar.
Moves to the second area of the document. Click on the first cell in the “Bet” column. Then click on the Enter button on the keyboard to enter data into the cell in which you previously set the equal sign.
Then there is an automatic transition to the first sheet. As you can see, the rate of the first employee from the second table is pulled into the corresponding cell. By placing the cursor on the cell containing the bet, we see that the usual formula is used to display data on the screen. But before the coordinates of the cell from which the data is output, there is the expression “Sheet2!”, which indicates the name of the document area where they are located. The general formula in our case looks like this:
Now you need to transfer data on the rates of all other employees of the enterprise. Of course, this can be done in the same way that we completed the task for the first employee, but given that both lists of employees are located in the same order, the task can be significantly simplified and its solution can be speeded up. This can be done by simply copying the formula onto the range below. Due to the fact that links in Excel are relative by default, when they are copied, the values shift, which is what we need. The copying procedure itself can be done using the fill marker.
So, place the cursor in the lower right area of the element with the formula. After this, the cursor should transform into a fill marker in the form of a black cross. Hold down the left mouse button and drag the cursor to the very bottom of the column.
All data from a similar column on Sheet 2 was pulled into the table on Sheet 1. When the data on Sheet 2 changes, it will automatically change on the first one.

Method 2: using a combination of INDEX - SEARCH operators

But what to do if the list of employees in the table arrays is not in the same order? In this case, as discussed earlier, one option is to manually establish a link between each of those cells that need to be linked. But this is only suitable for small tables. For massive ranges, such an option, at best, will take a lot of time to implement, and at worst, in practice it will not be feasible at all. But this problem can be solved using a combination of INDEX - SEARCH operators. Let's see how this can be done by linking data in tabular ranges, which were discussed in the previous method.

Select the first element of the “Bet” column. Go to the Function Wizard by clicking on the “Insert Function” icon.
In the Function Wizard, in the “Links and Arrays” group, find and select the name “INDEX”.
This operator has two forms: a form for working with arrays and a reference form. In our case, the first option is required, so in the next form selection window that opens, select it and click on the “OK” button.
The INDEX operator arguments window has been launched. The task of the specified function is to display the value located in the selected range in the line with the specified number. The general formula for the INDEX operator is:
INDEX(array;line_number;)

“Array” is an argument containing the address of the range from which we will extract information by the number of the specified line.

“Line number” is an argument that is the number of this very line. It is important to know that the line number should not be specified relative to the entire document, but only relative to the selected array.

“Column number” is an optional argument. We will not use it to solve our specific problem, and therefore there is no need to describe its essence separately.

Place the cursor in the “Array” field. After this, go to Sheet 2 and, holding down the left mouse button, select the entire contents of the “Bet” column.
After the coordinates are displayed in the operator window, place the cursor in the “Line number” field. We will output this argument using the MATCH operator. Therefore, we click on the triangle, which is located to the left of the function line. A list of recently used operators opens. If you find the name “SEARCH” among them, you can click on it. Otherwise, click on the very last item on the list – “Other functions...”.
The standard Function Wizard window opens. In it we go to the same group “Links and arrays”. This time, select the “SEARCH” item from the list. Click on the “OK” button.
The MATCH operator arguments window is activated. This function is designed to display the number of a value in a specific array by its name. It is thanks to this feature that we will calculate the row number of a certain value for the INDEX function. The MATCH syntax is presented as follows:
MATCH(lookup_value, lookedup_array;)

“Search value” is an argument containing the name or address of the cell in the third-party range in which it is located. It is the position of this item in the target range that should be calculated. In our case, the first argument will be links to cells on Sheet 1 in which the names of employees are located.

"Array to look through" is an argument that is a reference to an array in which the specified value is searched to determine its position. For us, this role will be played by the address of the “Name” column on Sheet 2.

"Matching type" is an optional argument, but unlike the previous statement, we will need this optional argument. It specifies how the operator will match the search value to the array. This argument can have one of three values: -1; 0; 1. For unordered arrays, select option “0”. This option is suitable for our case.

So, let's start filling out the fields of the arguments window. Place the cursor in the “Search value” field, click on the first cell of the “Name” column on Sheet 1.
After the coordinates are displayed, place the cursor in the “Viewable array” field and go to the “Sheet 2” shortcut, which is located at the bottom of the Excel window above the status bar. Hold down the left mouse button and select with the cursor all the cells in the “Name” column.
After their coordinates are displayed in the “Viewed Array” field, go to the “Matching Type” field and use the keyboard to set the number “0” there. After this, we return again to the “Viewed array” field. The fact is that we will copy the formula, as we did in the previous method. There will be a shift in addresses, but we need to fix the coordinates of the array being viewed. It shouldn't move. Select the coordinates with the cursor and press the function key F4. As you can see, a dollar sign has appeared in front of the coordinates, which means that the reference has turned from relative to absolute. Then click on the “OK” button.
The result is displayed on the screen in the first cell of the “Bet” column. But before we copy, we need to secure one more area, namely the first argument of the INDEX function. To do this, select the column element that contains the formula and move to the formula bar. Select the first argument of the INDEX operator (B2:B7) and click on the F4 button. As you can see, a dollar sign appears near the selected coordinates. Click on the Enter key. In general, the formula took the following form:
INDEX(Sheet2!$B$2:$B$7,MATCH(Sheet1!A4,Sheet2!$A$2:$A$7,0))
You can now copy using the fill handle. We call it in the same way that we talked about earlier and stretch it to the end of the table range.
As we can see, despite the fact that the order of the rows of the two linked tables does not match, nevertheless, all values are adjusted according to the last names of the employees. This was achieved by using a combination of INDEX-MATCH operators.

Method 3: Perform Mathematical Operations on Linked Data

Direct data linking is also good because it allows you not only to display values that are displayed in other table ranges in one of the tables, but also to perform various mathematical operations with them (addition, division, subtraction, multiplication, etc.).

Let's see how this is done in practice. Let's make it so that Sheet 3 will display general salary data for the enterprise without breaking it down by employee. To do this, employee rates will be pulled up from Sheet 2, summed up (using the SUM function) and multiplied by a coefficient using a formula.

Select the cell where the payroll calculation results will be displayed on Sheet 3. Click on the “Insert Function” button.
The Function Wizard window should launch. Go to the “Mathematical” group and select the name “SUM” there. Next, click on the “OK” button.
The SUM function, which is designed to calculate the sum of selected numbers, is moved to the arguments window. It has the following syntax:
SUM(number1;number2;…)

The fields in the window correspond to the arguments of the specified function. Although their number can reach 255 pieces, for our purpose only one will be enough. Place the cursor in the “Number1” field. Click on the “Sheet 2” shortcut above the status bar.
After we have moved to the desired section of the book, we select the column that should be summed. We do this with the cursor while holding down the left mouse button. As you can see, the coordinates of the selected area are immediately displayed in the field of the arguments window. Then click on the “OK” button.
After this, we are automatically moved to Sheet 1. As you can see, the total amount of employee rates is already displayed in the corresponding element.
But that is not all. As we remember, the salary is calculated by multiplying the bet by the coefficient. Therefore, we again select the cell in which the summed value is located. After that, go to the formula bar. We add a multiplication sign (*) to the formula in it, and then click on the element in which the coefficient indicator is located. To perform the calculation, click on the Enter key on the keyboard. As you can see, the program calculated the total wages for the enterprise.
We return to Sheet 2 and change the rate of any employee.
After this, we move again to the page with the total amount. As you can see, due to changes in the linked table, the total salary result was automatically recalculated.

Method 4: special insert

You can also link table arrays in Excel using paste special.

We select the values that will need to be “dragged” into another table. In our case, this is the range of the “Bid” column on Sheet 2. Click on the selected fragment right click mice. In the list that opens, select “Copy”. An alternative shortcut is Ctrl+C. After this we move to Sheet 1.
Having moved to the area of the book we need, we select the cells into which we will need to pull up the values. In our case, this is the “Rate” column. Right-click on the selected fragment. IN context menu In the “Insert Options” tool block, click on the “Insert Link” icon.
There is also Alternative option. By the way, it is the only one for older Excel versions. In the context menu, move the cursor to the “Paste Special” item. In the opened additional menu select the position with the same name.
This will open a Paste Special window. Click on the “Insert Link” button in the lower left corner of the cell.
Whichever option you choose, the values from one table array will be inserted into the other. When data in the source changes, it will also automatically change in the inserted range.

Lesson: Paste Special in Excel

Method 5: Linking tables in multiple workbooks

In addition, you can organize relationships between table areas in different books. This uses the Paste Special Tool. The actions will be absolutely similar to those that we considered in the previous method, except that when entering formulas you will have to navigate not between areas of the same book, but between files. Naturally, all connected books must be open.

Select the range of data that needs to be transferred to another workbook. Right-click on it and select “Copy” from the menu that opens.
Then we move to the book into which this data will need to be inserted. Select the desired range. Right-click. In the context menu, in the “Insert Options” group, select “Insert Link”.
After this, the values will be inserted. When data in the source workbook changes, the tabular array from the workbook will automatically update it. Moreover, it is not at all necessary that both books be open for this. It is enough to open just one workbook, and it will automatically pull up data from a closed linked document if changes have previously been made to it.

But it should be noted that in this case the insertion will be performed in the form of an immutable array. When you try to change any cell with inserted data, a message will pop up informing you that it is impossible to do this.

Changes to such an array linked to another book can only be made by breaking the link.

Breaking the relationship between tables

Sometimes you need to break the relationship between table ranges. The reason for this may be either the case described above, when you need to change an array inserted from another workbook, or simply the user’s reluctance to have data in one table automatically updated from another.

Method 1: breaking the connection between books

You can break the connection between books in all cells by essentially performing one operation. In this case, the data in the cells will remain, but they will already be static, non-updating values that are in no way dependent on other documents.

In a workbook in which values from other files are pulled up, go to the “Data” tab. Click on the “Edit connections” icon, which is located on the ribbon in the “Connections” tool block. It should be noted that if the current book does not contain links to other files, then this button is inactive.
The window for changing connections opens. We select from the list of linked books (if there are several of them) the file with which we want to break the connection. Click on the “Break connection” button.
An information window opens containing a warning about the consequences of further actions. If you are sure of what you are going to do, then click on the “Break ties” button.
After this, all links to specified file in the current document will be replaced with static values.

Method 2: Inserting Values

But the above method is only suitable if you need to completely break all connections between two books. What should you do if you need to separate related tables located within the same file? You can do this by copying the data and then pasting it in the same place as the values. By the way, in the same way you can break the connection between individual data ranges various books without breaking the common connection between files. Let's see how this method works in practice.

We select the range in which we want to delete the relationship with another table. We right-click on it. In the menu that opens, select “Copy”. Instead of these actions, you can type an alternative hotkey combination Ctrl+C.
Next, without deselecting the same fragment, right-click on it again. This time, in the list of actions, click on the “Values” icon, which is located in the “Insert Options” tool group.
After this, all references in the selected range will be replaced with static values.

As you can see, Excel has ways and tools to link several tables together. At the same time, tabular data can be located on other sheets and even in different books. If necessary, this connection can be easily broken.

We are glad that we were able to help you solve the problem.

Ask your question in the comments, describing the essence of the problem in detail. Our specialists will try to answer as quickly as possible.

Did this article help you?

An Excel table link is a formula that returns data from a cell in another workbook. When you open a workbook that contains links, Excel reads the latest information from the source workbook (update links)

Inter-table relationships in Excel are used to retrieve data both from other sheets in a workbook and from other Excel workbooks. For example, you have a table that calculates the total amount of sales. The calculation uses product prices and sales volume. In this case, it makes sense to create a separate table with data on prices, which will be pulled up using the connections of the first table.

When you create a relationship between tables, Excel creates a formula that includes the name of the source workbook, enclosed in parentheses, the name of the worksheet, and exclamation point at the end and a cell reference.

Creating links between workbooks

Open both workbooks in Excel
In the source workbook, select the cell that needs to be linked and copy it (keyboard shortcut Ctrl+C)
Go to the target workbook, right-click on the cell where we want to place the connection. Select from the drop-down menu Special insert
Special insert choose Insert connection.

There is another, simpler option for creating a relationship between tables. In the cell where we want to insert a link, put an equal sign (the same as for a regular formula), go to the source workbook, select the cell that we want to link, and click Enter.

You can use the copy and autocomplete tools for relationship formulas just as you do for regular formulas.

Before you create relationships between tables

Before you start spreading the knowledge of your big ideas, here are some tips for working with relationships in Excel:

Make easily traceable connections. Does not exist automatic search all cells containing links. Therefore, use a specific format to quickly identify links to other tables, otherwise the document containing the links may grow to such an extent that it is difficult to maintain.

Automatic calculations. The source workbook must work in automatic calculation mode (set by default). To switch the calculation parameter, click the tab Formulas to the group Calculation. Select Calculation options –> Automatic.

Updating connections

For manual update connections between tables, go to the tab Data to the group Connections. Click the button Change connections.

In the dialog box that appears Changing connections Update.

Break links in Excel workbooks

Unlinking a source will cause existing link formulas to be replaced with the values they return. For example, the link =Prices!$B$4 will be replaced by 16. Breaking the link cannot be undone, so before performing the operation, I recommend saving the book.

Go to the tab Data to the group Connections. Click the button Change connections. In the dialog box that appears Changing connections select the connection you are interested in and click on the button Break the connection.

If you are not yet familiar with pivot tables, then start with this article.

Problem

It happens that the analyzed data comes to us in the form of separate tables, which, nevertheless, need to be linked. MS Access can easily do this, but in Excel you always had to use VLOOKUP type formulas for this. However, starting with Excel 2013, we have the opportunity to use several tables linked by key fields as a source when building a pivot table.

Example

In our example, we have 4 tables: Orders, Order Lines, Products, Customers.

Order table:

Order Line Table:

Product Table

Clients table

The source tables are designed as smart tables: Orders, OrderLines, Goods and Clients.

It is quite obvious that the Orders and OrderLines tables can be linked by the Order_ID field, the Orders and Clients tables by the Customer_ID field, and the OrderLines and Goods tables by the Product_ID field.

Download example

Creating a Data Model

Let's create a pivot table based on any of the existing tables.

Select Pivot Table from the Insert menu. In the said dialog box, we see the option to Add this data to the data model. We could choose it, but I recommend another, more convenient way. Just click OK.

In the PivotTable Fields panel that appears, you see the inscription OTHER TABLES...

Let's press it. The following question will appear:

If you start selecting fields, after a while a CREATE button will appear in the list of fields...

Let's click it and create connections between our tables. This creates a connection between the Orders table and OrderLines. Please note that Excel can create a one-to-one or one-to-many relationship. Moreover, the table with “many” must be indicated first, otherwise Excel swears and offers to swap them.

We create other connections in the same way.

The Manage Relationships dialog box can be accessed through the ANALYSIS ribbon, Relationships command

To see more fields in the PivotTable Fields panel, you can use the Tools button (shaped like a gear) to select this view:

The result will be like this:

As a result, all our tables are now connected and you can generate, for example, such a report.

Unfortunately, if the source book was deleted/moved or renamed, the connection will be broken. Also, the connection will be lost if you move the target file (containing the link). If you only send the final file by mail, the recipient will also not be able to update the links.

How to break the connection

One way to solve this problem is to break the connection. If there is only one link in the file, then this is quite simple:

Go to the tab Data.
Select a team Change connections In chapter Connections.
Click Break connection.

IMPORTANT! When the connection is broken, all formulas referring to the source book will be converted to values! Cancellation of this operation is impossible!

How to unlink all books

For convenience, you can use a macro that will break connections with all books. The macro is part of the VBA-Excel add-in. To use it you need:

VBA code

The code for a macro that deletes all links to a book is presented below. You can copy it to your project.

Sub UnlinkWorkBooks() Dim WbLinks Dim i As Long Select Case MsgBox("All references to other workbooks will be removed from this file, and formulas referencing other workbooks will be replaced with values." & vbCrLf & "Are you sure you want to continue? ", 36, "Break the link?") Case 7 " No Exit Sub End Select WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) If Not IsEmpty(WbLinks) Then For i = 1 To UBound(WbLinks) ActiveWorkbook.BreakLink Name: =WbLinks(i), Type:=xlLinkTypeExcelLinks Next Else MsgBox "In this file there are no links to other books.", 64, "Links to other books" End If End Sub

How to break connections only in the selected range

Sometimes there are many links in a book and there is a concern that removing a link might remove the extra one. To avoid this, you can use the add-in to delete connections only in the selected range. For this.

Information in a spreadsheet Microsoft Excel can be combined with data extracted from the drawing.

By creating a connection with electronic Excel table You can include information from the entire sheet or just part of it, i.e. named range. In the External Data Link dialog box, you can do the following:

Set a data link by selecting the XLS file to which you want to link the data extracted from the drawing.
Set the mapping between the extracted drawing data and the spreadsheet data.
Select the columns in the XLS file that you want to merge with the extracted drawing data.

The Data Correction page provides access to dialog box"Link to External Data", where you can link the extracted data to the entire Excel spreadsheet or to a range of rows and columns.

To create a link to a spreadsheet, Microsoft Excel must be installed on the computer performing the data extraction. If not installed Microsoft program Excel or XLS file is not saved locally on the computer on which the data is being retrieved, the data cannot be linked to.

After you change a linked spreadsheet, such as adding a row or column, you can update the table in the drawing to reflect that change by using the LINK UPDATE command. Likewise, if you change a table in a drawing, the associated spreadsheet can be updated using the same command.

Note: To remove a data link, use the LINK command. Links to data from the palette External links"Cannot be deleted.

About data linking and matching

Data linking involves selecting a Microsoft Excel spreadsheet to combine all or part of the table with the extracted drawing data. additional information To learn about data binding, see "Creating a table link to external data."

The data mapping process creates a relationship between two sets of data: the data in the extracted drawing data table column and the data in the Excel spreadsheet column.

The following example examines a simple specification. Three columns of data are retrieved from the drawing. These columns contain the following data: Part no., Editorial And Quantity. You can include two additional columns of data from a spreadsheet in your specification. These columns contain the following data Price And Provider.

IN in this example the spreadsheet contains a column of part numbers that has a header Catalog number and corresponds to the column Detail number in the extracted data table. In the External Data Link dialog box, select "Detail number" as a column for drawing data and "Catalogue number" as a column for external data. Because there is at least one corresponding data cell in both the drawing data column and the external data column, the data extraction function knows how the two sources are related.

You can check the consistency of the data by clicking the Check Compliance button in the External Data Link dialog box. If the match is incorrect, a warning message is displayed indicating the reason. In some cases, you may not be able to correctly create a link between drawing data and spreadsheet data.

Validating matched data

A data match failure means that a valid match between the drawing data and the external data (in the spreadsheet) cannot be found. The following error messages are displayed.

No matches were found between the values in the drawing data and the external data.

If the problem cannot be resolved, external data merging will not occur. Compare the spreadsheet column data with the drawing data and try to find corresponding values. For example, drawing data and external data can be set to different meanings accuracy.

The data column in external data does not contain unique values.

An external data column contains one or more cells with the same data. In this situation, it is impossible to unambiguously define the correspondence between the drawing data and external data.

A linked table is a collection of data that can be managed as a single unit.

Use the button to create a linked table "Format as table" on the panel "Styles" tapes "Home".

Select the desired style for the future table and specify the range of cells on the basis of which the related table will be created.

After setting all the parameters, we will get the associated table. Buttons with arrows will appear next to the column headings (similar to filtering), and a contextual tool will appear in the program window "Working with tables" containing the tape "Constructor".

Each linked table is given a unique name. The default is "Table_number". You can change the table name in the panel "Properties".

On the panel "Table styles" You can, if necessary, change or customize the style of the linked table.

You can turn a linked table back into a regular range of cells. There is a button for this "Convert to Range" on the panel "Service" contextual feed "Constructor".

As mentioned earlier, next to the column headers there are sort and filter buttons that allow you to sort the data and apply filters.

In a linked table, you can calculate column totals. To do this, check the box "Total Row" on the panel "Table Style Options".

Using the drop-down list, you can select the desired value of the final function.

You can add/remove rows and columns to a linked table.

This can be done in several ways.

1. Use the button "Resize table" on the panel "Properties".

2. Place the cursor in the cell of the linked table, next to which you need to add a new column (row) and in the panel "Cells" tapes "Home" use the button "Insert".

Popular

How to number pages in OpenOffice

« From: Belgorod Registered: 2015.03.18 Posts: 550 Likes: 81 Topic: Open Document How to number pages Page numbers in a text... »

The largest public pages on VKontakte Cook it!

« 1. Collections of movies, Public films with those “save them on your wall so you don’t forget to watch them.” For convenience, in publications along with lists... »

Samsung Galaxy A9 Star Pro: the first smartphone with a quad camera Information about the type of speakers and audio technologies supported by the device

« The Koreans presented the world's first smartphone with four cameras - Samsung Galaxy A9 (2018). The price at the start of sales is 39,990 rubles. Someday this... »