How to Use Reference of Worksheets in Excel (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

📌 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.

reference worksheets in same workbook

  • In the referenced worksheet, choose the cell (cell G6 here) we wish to refer to and press Enter.

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.

drag the fill handle to fill up the rest of the cells

Read More: Excel VBA Examples with Cell Reference by Row and Column Number


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.

📌 Steps: 

  • 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.
='C:\Users\Desktop\MARCH'!$G$6
  • 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.

Note:

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.

formula in the creation of an external reference to an open workbook

the external workbook is closed, Excel automatically inserts a reference to the file path as well


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.

='[FileName]SheetName!CellAddress

📌 Steps: 

  • We will click on the cell where we would like the reference to go.
  • To begin constructing the reference, enter =.

To begin constructing the reference

  • We will select the open workbook after putting the cursor on Excel in the taskbar.

Reference Sheets from Different Workbooks

  • After that, we just select the required cell that we want in the summary table.

select the required cell

  • Now, we will drag the fill handle down to fill the rest of the cells.

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.

the final summary table

  • 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.

the referencing formula for the opened workbook will automatically be changed

  • 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.

Excel has taken the file directory of the source workbook to do the referencing

Read More: How to Use Cell Value as Worksheet Name in Formula Reference in Excel


Similar Readings


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.

📌 Steps:

  • 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.

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.

Give the cell a name in the New Name dialog box


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.

entering the cell's name in the summary worksheet

  • Press Enter, and we will get a cell value like the image below.

get cell value by calling name


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:

='source workbook.xlsx'!Hamburger

Note:

If we don’t have space in our workbook, we can use the formula like this:

=sourceworkbook.xlsx!Hamburger

Reference Defined Name from Other Worksheets


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:

='C:\Users\Desktop\source workbook.xlsx'!Hamburger

accessing a named range in a closed workbook

Read More: [Fixed!] Relative Cell Reference Not Working in Excel


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:

=INDIRECT("'"&$G$6&"'!"&G7)

Here,

  • Sheet’s name is in $G$6
  • Cell to pull data from in G7

an Excel Indirect formula

Read More: How to Use Cell References in Excel Formula (All Possible Ways)


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):

=INDIRECT("'["&$H$8&"]"&$I$8&"'!"&J8)

Here,

  • Book name in $H$8
  • Sheet name in $I$8
  • Cell address in J8

to construct a standard reference to another book

Read More: Excel VBA: Cell Reference in Another Sheet (4 Methods)


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.

Example:

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.

Example:

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.

Example:

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.

Example:

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.

Example:

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.

Example:

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.

Read More: Relative and Absolute Cell Address in the Spreadsheet


Conclusion

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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo