In our previous post, we explained how to reference cells in current worksheet, other worksheet and other workbook while writing VBA code. This post talks about referencing cells in current worksheet, other worksheet and other workbook while writing excel formulas.
Referencing Cells In Current WorkSheet
One of our previous post explained about relative, absolute & mixed cell references in excel. Just to refresh memory, Cell Reference in Excel is denoted by two letters, where first letter denotes the Column and second letter denotes the Row. A1, $A$1, A$1 are examples of relative, absolute and mixed cell references respectively. These cell notations are used when the cells are in the same worksheet.
Referencing Cells in Other WorkSheet
To reference a cell in a worksheet other than the active or current worksheet, cell reference is precedeed by WorkSheet name followed by an exclamation point as shown below:
1 2 3 4 5 |
=Sheet2!A1 =Sheet2!$A$1 =Sheet2!A$1 |
Referencing Cells in Other Workbook
To reference a cell in a workbook other than the active or current workbook, cell reference is precedeed by WorkBook name in square brackets, followed by WorkSheet name and an exclamation point as shown below:
1 |
=[Test.xlsx]Sheet1!A1 |
Note: If the workbook name includes any spaces, WorkBook and WorkSheet name must be enclosed in single quotation marks.
1 |
='[Test Excel.xlsx]Sheet1'!A1 |
Referencing Cells in Closed Workbook
If the linked workbook is closed, complete path of workbook should be used as below:
1 |
='C:\Excel\[Test Excel.xlsx]Sheet1'!A1 |
Hope you find the information shared here useful and informative. Feel free to leave your queries, suggestions, feedback in the comments section below.