Ference of Sheet 1 Data in Sheet 2 in Excel

How to Auto Copy Sheet 1 Data in Sheet 2 in Excel

The main use of this concept is if you add some data in sheet 1 it automatically updates in sheet 2. Here we discuss bout this in detail. To reference a cell or range of cells in another worksheet in the same workbook, put the worksheet name followed by an exclamation mark (!) before the cell address.

In other words, in an Excel reference to another worksheet, you use the following format. Reference to an individual cell:

Sheet_name!Cell_address

we can see in this example: refer cell B1 of sheet 1 in Sheet2, we need to type type Sheet2!B1

Syntax for Reference to a range of cells

Sheet_name!First_cell:Last_cell

Example to Reference to a range of cells

Sheet_name!First_cell:Last_cell

For example, to refer to cells A1:A15 in Sheet2, you type Sheet2!A1:A15.

Note: If the worksheet name includes spaces or non-alphabetical characters, you must enclose it in single quotation marks. For example, an external reference to cell A1 in a worksheet named Monthly Statement should read as follows: 'Monthly Statement'!A1.

How to reference another workbook in Excel

In Microsoft Excel formulas, external references to another workbook are displayed in two ways, depending on whether the source workbook is open or closed.

External reference to an open workbook

When the source workbook is open, an Excel external reference includes the workbook name in square brackets (including the file extension), followed by the sheet name, exclamation point (!), and the referenced cell or a range of cells. In other words, you use the following reference format for an open workbook reference

[Workbook_name]Sheet_name!Cell_address

For example, here's an external reference to cells B2:B5 on sheet Jan in the workbook named Sales.xlsx:

[Sales.xlsx]Jan!B2:B5

If you want, say, to calculate the sum of those cells, the formula with the workbook reference would look as follows:

=SUM([Sales.xlsx]Jan!B2:B5)

External reference to a closed workbook

When you reference another workbook in Excel, that other workbook does not necessarily need to be open. If the source workbook is closed, you must add the entire path to your external reference.

For example, to add up cells B2:B5 in the Jan sheet from Sales.xlsx workbook that resides within the Reports folder on drive D, you write the following formula:

=SUM(D:\Reports\[Sales.xlsx]Jan!B2:B5)