قالب وردپرس درنا توس
Home / Tips and Tricks / Cross-reference of cells between Microsoft Excel worksheets

Cross-reference of cells between Microsoft Excel worksheets

  Excel Logo

In Microsoft Excel, referencing cells in other worksheets, or even in other Excel files, is a common task. This may seem a bit intimidating and confusing at first, but once you understand how it works, it's not that hard.

This article explains how to reference another sheet in the same Excel file and how to point to another Excel file. We will also look at topics such as referring to a range of cells in a function, simplifying the use of defined names, and using VLOOKUP for dynamic references.

Referencing another sheet in the same Excel file [1
9659005] A basic cell reference is written as a column letter followed by the row number.

The cell reference B3 thus refers to the cell at the intersection of column B and row 3.

When referring to cells in other sheets, the cell name is preceded by the name of the other sheet. For example, here's a reference to cell B3 in a sheet name "January".

  = January! B3 

The exclamation mark (!) Separates the sheet name from the cell address.

If the sheet name contains spaces, then you must enclose the name with single quotes in the reference.

  = & # 39; January Sales & # 39 ;! B3 

To create these references, you can enter them directly into the cell. However, it is easier and more reliable to let Excel have the reference for you.

Enter an equal sign (=) in a cell, click the Sheet tab, and then click the cell you want to refer to.

Excel writes the reference for you in the formula bar.

 Sheet Reference in Formula

Press Enter to complete the formula.

Referencing another Excel file

You can use the same method to reference cells in another workbook. Make sure the other Excel file is open before you start typing the formula.

Enter an equal sign (=), go to the other file, and then click the cell in the file you want to refer to. Press Enter when you're done.

The completed cross-reference contains the name of the other workbook in square brackets, followed by the sheet name and the cell number.

  = [Chicago.xlsx] January! B3 

If the file or sheet name contains spaces, then you must enclose the file reference (including the square brackets) in single quotes.

  = & # 39; [New York.xlsx] January & # 39 ;! B3 

 Formula referring to another workbook

In this example, $ ($) is displayed below the cell address. This is an absolute cell reference (more about absolute cell references).

When referencing cells and ranges in different Excel files, the references are set as absolute references by default. If necessary, you can change this to a relative reference.

If you look at the formula while the referenced workbook is closed, it contains the entire path to that file.

 Full file path of the workbook in the formula

Although creating references to other workbooks is easy, they are more prone to problems. Users who create or rename folders and move files can override these links and cause errors.

If possible, storing data in a workbook is more reliable.

Cross Reference to a Cell Range in a Function

References to a single cell is useful enough. However, you might want to write a function (for example, SUM) that points to a range of cells in another worksheet or workbook.

Start the function as usual, and then click on the sheet and cell range in the previous examples.

In the following example, a SUM function sums the values ​​from the range B2: B6 in a worksheet named Sales.

  = SUM (Sales! B2: B6) 

 Sheet cross-reference in summary function

Using defined names for simple cross-references

In Excel, you can name a cell or range of cells. This makes more sense than a cell or range address when you look at them. If you use a lot of references in your spreadsheet, naming these links makes it easier for you to understand what you've done.

This name is unique even for all worksheets in this Excel file.

For example, we could have a cell & # 39; ChicagoTotal & # 39; and then the cross-reference would be:

  = ChicagoTotal 

This is a more useful alternative to a standard reference like this:

  = Sales! B2 

It's easy to create a distinguished name. Start by selecting the cell or range of cells you want to name.

Click in the name box in the upper-left corner, enter the name you want to assign, and press Enter.

 Defining a name in Excel

Can not use spaces when creating defined names. Therefore, in this example, the words were put together in the name and separated by a capital letter. You can also use words like a hyphen (-) or an underscore (_) to separate words.

Excel also has a name manager that makes it easier to monitor these names in the future. Click Formulas> Name Manager. In the Name Manager window you will see a list of all defined names in the workbook, where they are located and what values ​​they currently store.

 Name Manager to Manage the Defined Names [19659003] You can then use the buttons above to edit and delete these defined names.

Formatting Data as a Table

When you work with a large list of related data, you can simplify the Format As Table feature in Excel to reference data in it.

Take the following simple table.

 Small list of product sales data

These can be formatted as a spreadsheet.

Click a cell Go to the Home tab of the list, click the Format as Table button, and then select a style.

 Formatting an area as a table

Make sure the range of cells is correct and your table contains headers.

 Confirm the range to use for the table.

You can then assign a meaningful name to your table on the Design tab.

 Name your Excel spreadsheet

If we then had to sum up Chicago's sales, we could refer to the spreadsheet with their name (from any sheet), followed by a square bracket ([)todisplayalistoftablecolumns

 Use structured references in formulas

Select the column Double-click it in the list and enter a closing square bracket. The resulting formula would look something like this:

  = SUM (Sales [Chicago]) 

You can see how tables make referencing data easier for aggregation functions such as SUM and AVERAGE than standard sheet references.

This table is small for demonstration purposes. The larger the table and the more leaves you have in a workbook, the more benefits you will see.

Using the VLOOKUP function for dynamic references

The references used in the previous examples have all been corrected for a particular cell or range of cells. That's great and often enough for your needs.

But what can change if the cell you refer to is changed as new rows are inserted, or if someone else sorts the list?

You can not guarantee in these scenarios. The desired value is still in the same cell that you originally referenced.

In these scenarios, an alternative is to use a search function in Excel to find the value in a list. This makes it more durable against changes to the worksheet.

In the following example, we use the VLOOKUP feature to look up an employee by using his employee ID on another worksheet and then returning his start date.

Below is the example list of employees.

 List of Employees

The VLOOKUP function searches the first column of a table and then returns information from a specified column to the right.

The following VLOOKUP function searches for the employee ID entered in the above list in cell A2 returns the associated date from column 4 (fourth column of the table).

  = VLOOKUP (A2, Employees! A: E, 4, FALSE) 

 The VLOOKUP function

The following shows how this formula searches the list and returns the correct information.

 The VLOOKUP function and how it works

The big thing about this VLOOKUP over the previous examples is that the employee does this is also found when the list is in the specified order changes.

Note: VLOOKUP is an incredibly useful formula, and we've only scratched the surface of the value in this article. For more information on using VLOOKUP, see our article on this topic. In this article about Computergaga (also written by Alan Murray) you will learn even more tricks for use.

In this article, we looked at several ways to cross-reference between Excel worksheets and workbooks. Choose the approach that is right for your job and with which you can work well.

Source link