Before understanding this topic, you have to understand cell references in a formula. Say I have a formula like this ‘=B3*C3+D3’. In this formula there are three cell references, they are B3, C3, and D3. Every reference in a formula links with a cell in an Excel worksheet. In other words, every cell has a unique reference. B3 can link to only one cell in the worksheet. You can use the cell reference of one worksheet in the same worksheet or with another worksheet. So today we are going to show you some easy steps to reference a cell from a different worksheet in Excel.
How to Reference a Cell from a Different Worksheet in Excel: Step-by-Step Procedures
Using a real-time example, we shall make clear to you how to use cell references with multiple worksheets. In our example, we have two worksheets, they are ‘Menu Order’ and ‘Total Cost’. We shall use the cell reference of worksheet ‘Menu Order’ with a cell in our other worksheet ‘Total Cost’.
It’s the ‘Total Cost’ sheet.
Read More: How to Reference Cell in Another Sheet Dynamically in Excel
Step 1: Select a Cell to Insert Cell Reference
- Firstly type = in cell C5 of the sheet ‘Total Cost’.
- Next, click on the other sheet name- ‘Menu Order’. It will take you to that sheet.
Read More: How to Find and Replace Cell Reference in Excel Formula
Step 2: Choose the Cell from Another Sheet
- Now select the cell that’s cell reference you want to use. We clicked cell G18.
- Finally, just hit the ENTER button.
After that, the formula will look like this-
Also, you can insert the whole formula by writing it instead of selecting.
NOTE: If a worksheet name contains space, then you have to use single quotation marks(”) around the name, otherwise use the name direct in the formula.
Here’s the value of that cell.
Attention 1: If you rename your worksheet name later, the formula will be updated automatically with the new name of the worksheet.
Attention 2: If you type the worksheet name wrongly, then a #REF! error will be visible. Click the yellow error button to edit the worksheet name with the ‘Edit in Formula Bar‘ option.
Read More: How to Use Cell Value as Worksheet Name in Formula Reference in Excel
How to Reference a Cell from Another Excel File
Now we’ll learn to use a cell reference from another file in Excel which means the sheet will remain in another different worksheet. And of course, it’s not so difficult, we’ll have to add the file name and file path in the formula just. For that, we modified the dataset and placed the sheet ‘Menu Order’ in a new workbook named- Practice File.
1. Reference a Cell from an Open File
First, we’ll show how to reference a cell if the worksheet is remain open. If the file remains open then we’ll need the file name just, no need for the file path.
Steps:
- First, in Cell C5 of the sheet ‘Total Cost’ and type =.
- Later, navigate to your another file. We’ll go to the ‘Practice File’ sheet for our example.
- Then from the Menu Order sheet, select Cell G18 and press the ENTER button.
- The final formula will look like this-
Here also, we can insert the whole formula by writing it instead of selecting.
Now see, the value of the reference is now added to our workbook.
Let’s change one value from that file. We inserted 100 in cell G14.
Have a look, here the formula is updated automatically.
Read More: How to Use OFFSET for Cell Reference in Excel
2. Reference a Cell from a Closed File
To reference a cell from a different closed workbook, we’ll have to just add the file path with the previous formula. Look at the image below, we added the file path before the file name of the previous formula.
That’s it. Rest is done by Excel itself to fetch the value from the cell reference we have made from the sheet of the different workbook.
Read More: How to Reference Cell by Row and Column Number in Excel
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
Conclusion
That’s all for the article. I hope the procedures described above will be good enough to reference a cell from a different worksheet in Excel. Feel free to ask any question in the comment section and please give me feedback.
No comments:
Post a Comment