This article will illustrate how to use reference worksheets in MS Excel. In Excel, data are stored in cells in various worksheets. Each worksheet has as many as 17,179,869,184 cells, and there are 255 sheets you can add to an Excel workbook. While dealing with MS Excel, we very often need to use references to worksheets from either the current workbook or other workbooks (opened or closed). We will have a firm discussion on all these.
Download Practice Workbook
Download the following practice workbook to practice while reading this article.
5 Examples of Using Reference of Worksheets in Excel
Now, we will see several examples of using worksheet references in Excel.
1. Reference Worksheets from Same Workbook
It’s easy to use reference of worksheets from the same workbook you are working with. Just apply the following steps.
- Click on the cell where we would like the reference to go. Here, in cell C5.
- To begin constructing the reference, enter =. Now we will select any of our sheets like the image below.
- In the referenced worksheet, choose the cell (cell G6 here) we wish to refer to and press Enter.
- Finally, drag the fill handle to fill up the rest of the cells in the column.
2. Reference Sheets from Different Workbooks
While referencing different workbooks, they can be opened or closed. We will see both cases here.
2.1 Closed Workbook
When we open an additional worksheet and refer to it, we also have to supply the file name with sheet name and cell/range. So if the source workbook is not opened, Excel has no notion wherein Excel should go for the cells/range we referred to. For that reason, whenever we try to create a reference to a closed workbook, we also have to mention the file path.
- A reference to cell G6 in the MARCH worksheet of the Example File workbook is provided below. Because this file is not open, furthermore the formula below also refers to where the file is stored.
- The following elements are included in the reference as mentioned earlier:
♣ File Path – the location of the external file is placed
♣ Filename – external workbook name
♣ Sheet Name – the name of the sheet where G6 is located.
♣ Cell/Range Location -the cell, in this case, is G6.
When we create external references to open workbooks and then close them, you would notice that the reference changes automatically. After you close the external workbook, Excel inserts a reference to the file path as well automatically.
2.2 Opened Workbook
When referencing an external open workbook, we must include the workbook name, worksheet name, and cell/range address. The format to use when referring to an external open worksheet is shown below.
- We will click on the cell where we would like the reference to go.
- To begin constructing the reference, enter =.
- We will select the open workbook after putting the cursor on Excel in the taskbar.
- After that, we just select the required cell that we want in the summary table.
- Now, we will drag the fill handle down to fill the rest of the cells.
- The cost in March is added to the summary table and the summary table will look like the below image.
- Now some interesting point to note is that the referencing formula for the opened workbook will automatically be changed by Excel after closing the source workbook.
- Excel has taken the file directory of the source workbook to do the referencing. The fact is that we also have used the very same formula for referencing a cell that is in the closed workbook.
- How to Reference Text in Another Cell in Excel (14 Ways)
- Excel VBA: R1C1 Formula with Variable (3 Examples)
- How to Find and Replace Cell Reference in Excel Formula
- [Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)
- How to Use Variable Row Number as Cell Reference in Excel
3. Reference Defined Name from Other Worksheets
If we have used this defined name in many formulae and need to modify the reference, we just need to do it once. Here are the procedures for making a named range for a group of cells.
- We will select all of the cells that we wish to include in the named range.
- Navigate to the Formulas tab.
- In the Defined Names group, select the Define Name option.
- Give the cell a name in the New Name dialog box; in the figure below, we called a cell Big-Mac. Remember that there can’t be any spaces in the name.
3.1 Same Workbook
- We have named a cell in another workbook but now we can call that cell by just entering the cell’s name in the summary worksheet.
- Press Enter, and we will get a cell value like the image below.
3.2 Other Workbook
3.2.1 Opened Workbook
For example, if we have an Excel workbook named source workbook.xlsx and a named range called Hamburger, we can use the following formula to acquire the total of this cell from another workbook:
If we don’t have space in our workbook, we can use the formula like this:
3.2.2 Closed Workbook
If we are accessing a named range in a closed workbook, we must also mention the file location. As an example, consider the following formula:
4. Excel Reference Cell in Another Sheet Dynamically Based on Value
We can use references from worksheets in the INDIRECT function. Let’s see some examples.
4.1 Same workbook
Assume, we have some vital data in the APRIL worksheet that we want to extract into a Summary Datasheet. The screenshot below shows how an Excel INDIRECT function may perform this task:
- Sheet’s name is in $G$6
- Cell to pull data from in G7
4.2 Another Workbook
Now we intend to construct a standard reference to another book (apostrophes are used if our book and/or sheet titles include spaces):
- Book name in $H$8
- Sheet name in $I$8
- Cell address in J8
5. Reference Worksheet by Name in VBA
You can use worksheets’ references in VBA in different ways. Here we will give some examples.
5.1 Reference with Code Name of Sheet
Even if you change the sheet names, Excel can still recognize the sheets by their code names (sheet1, sheet2, and so on). So, you can reference worksheets using their code names.
Sheet1.Range("F9").Value = 79
>> This macro will return value 79 (numeric value), in cell F9 of sheet1.
5.2 Reference By Sheet Name
If you change the worksheet names, you can still reference them in VBA. In that case, you have to put the sheet name in double quotes like the following macro.
ThisWorkbook.Worksheets("JANUARY").Range("F9").Value = 79
>> This macro will find the worksheet named JANUARY from the workbook you are present now and return 79 in cell F9.
5.3 Reference to Currently Viewed Worksheet
If you move from the currently viewed worksheet, you can still reference them in VBA. In that case, you have to use the following macro.
ActiveSheet.Range("F9").Value = 79
>> This macro will find the currently viewed worksheet from the workbook you are present now and return 79 in cell F9.
5.4 Reference According to Worksheet Position
If you move among multiple worksheets, you can still reference them according to the worksheet position in VBA. In that case, you have to use the following macro.
ThisWorkbook.Worksheets(1).Range("F9").Value = 79
>> This macro will find the first worksheet from the workbook you are present now and return 79 in cell F9.
5.5 Reference of Last Worksheet
Whether you move among multiple worksheets, you can still reference them according to the worksheet position in VBA. In that case, you have to use the following macro.
ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count).Range("F9").Value = 79
>> This macro will find the last worksheet from the workbook you are present now and return 79 in cell F9.
5.6 Reference Worksheet in Another Workbook
Whether you move among multiple workbooks, you can still reference them in VBA. In that case, you have to use the following macro.
Workbooks("relative-absolute-references").Worksheets("Summary Data").Range("F9").Value = 79
> This macro will find the Summary Data worksheet from the workbook named relative-absolute-references and now return 79 in cell F9 in that worksheet.
These procedures and methods will allow you to use worksheet references in Excel. You may also download the workbook and use it for your own practice. If you have any questions, problems, or recommendations, please leave them in the comments area. For more such articles, please visit our blog.
- Excel VBA: Insert Formula with Relative Reference (All Possible Ways)
- How to Use Relative Cell Reference in Excel (5 Suitable Examples)
- Excel VBA: Get Cell Value from Another Workbook without Opening
- How to Lock a Cell in Excel Formula (2 Ways)
- Reference Another Sheet in Excel (3 Methods)
- How to Use Table Reference with Excel VBA (20 Examples)
- Creating and Copying Formula Using Relative References in Excel