CONSULTATION IN ANOTHER BOOK

CONSULTATION IN ANOTHER BOOK

One option that gives us excel is the ability to perform queries in other books, such as data search an employee or sales you made that employee for a certain period of time and which are contained in another book.

In excel performing an operation of this type is identified with the name EXTERNAL REFERENCE can create references are following certain steps.

 

You can reference the contents of cells in another workbook using an external reference. An external reference (also called link ) is a reference to a cell or range of cells on a worksheet in another workbook in Excel, or a reference to a defined name in another book. 

You can reference ranges specific cells, a specific name ranges of cells, or define a name for the external reference.

The formulas with external references to other books are displayed in two ways, depending on whether the source workbook, which provides data to the formula, is opened or closed in Excel.

When the source workbook is open in Excel, external reference includes the workbook name in square brackets ([]), followed by the name of the worksheet, an exclamation mark (!) And cells on which depends the formula. 

For example- the following formula adds cells C10 : C25 Budget.xls book.

External Reference

 
= SUM ([Presupuesto.xlsx] Annual C10 : C25)

If the source workbook is closed in Excel, external reference includes the entire path.

External Reference

 
= SUM ('C: \ Report \ [Presupuesto.xlsx] Annual' C10 : C25)

The formulas linked to a defined name in another workbook using the workbook name followed by an exclamation mark (!) And name. 

For example- the following formula sums the cells in the range Presupuesto.xlsx Book Sales.

= SUM ([ Presupuesto.xlsx] Sales)

Where can effectively use external references

External references are especially useful when it is not practical to hold together in the same workbook sheet models of large calculation.

•    Combine data from several books

•    Create different presentations of data

•    Simplify large and complex models