While working on Excel data, you must have come up with a situation when you need to transfer information from another sheet to your existing sheet. To do this, you often need to face some issues because it could be tricky at times. Here in this post, you will get to know how you can excel reference another sheet or workbook without creating a mess.
When you talk about referencing another sheet in Excel, it clearly shows a connection between the existing sheet and another sheet or workbook. By now, you can link a cell or cell range other than your current sheet. You always have to have an Excel reference to another sheet when data is needed from another sheet. Cells are linked to ensure the formula is real-time and can be used dynamically.
References could be of two kinds; external reference and internal reference.
An external reference is when cells or cell ranges make references to other worksheets. Basically, external references are used to make a sheet that finds out the totals from other sheets.
How Excel Reference Another Sheet or Workbook
Let’s understand the phenomena using different examples:
How to Reference in the Same Worksheet
It may seem difficult to reference an Excel cell or cell range from the same sheet but in reality, this is not that tricky thing. At first, you have to choose the cell that is needed from the resulting cell.
Now, suppose that in cell E8 you need to add the data from the B2 cell.
Now, you can see in cell B2 the Apple Prices that’s why a similar number is required that can be linked to cell E8. Add an equal sign in cell E8.
You can choose a certain cell using the mouse or else you can enter B2 directly. Now, press the Enter key from the keyboard and you will see the value from cell B2 to E8.
Cell E8 is dependent entirely on cell B2, that’s why you will see changes in cell B2 that directly affect the E8 cell with the exception of cell formatting.
How to Reference Another Workbook
Apart from that you can even reference cells from another different file.
Put the cursor where you need to add the reference.
Open the workbook containing the data that you need for reference right before putting the formula.
Add = and your reference will start building.
From the taskbar menu, choose the Excel icon and select the workbook containing cells that are needed for reference.
Choose the cells for reference and press ENTER key.
Remember that your formula will not work if the file you have selected for reference is moved, renamed, or deleted for any reason.
You will notice the value from another workbook that appears in the worksheet. When you see another workbook is used for reference in a formula, the file name will be there in brackets like this:
[]
Reference to Another Sheet using an Array Formula in Excel
Using the Array formula will work in a similar way. For this, you need to highlight the cell range that is needed for the reference so that you can add data. Press = the key from the keyboard.
Choose the Source sheet and highlight the cells that you need.
Press the SHIFT + ENTER keys from the keyboard.
You will notice the formula as a range and each relevant column appears with the value from the corresponding column used in the source workbook.
Things to Consider
Whenever you need to reference cells from the same worksheet, remember that you need to have cell addresses only.
On the other hand, whenever you need to reference a cell from another sheet of the same workbook, you need to have the worksheet name that is used for reference along with the cell address in the worksheet.
When the cell is referred from another workbook, you will get an absolute reference, $A$2.
On the other hand, whenever the cell is referred from another Excel sheet of the same workbook, you will get a relative Excel reference.
