We can create a dynamic worksheet so that cell references can be variable instead of hand-keying them. A dynamic worksheet can be created with the **INDIRECT** function. The easy steps below will walk through the process.

*Figure 1: Result of the Dynamic Worksheet Reference*

**Syntax**

**=INDIRECT(sheet_name&"!A1”)**

**Formula**

**=INDIRECT(B4&"!A1")**

**Setting up the Data**

We will set up the data by doing the following:

- We will name the first sheet as
**reference worksheet**by right-clicking the plus sign enclosed in a circle as shown in figure 2 and click on**rename** - We will create
**Sheet1**to**Sheet4**by clicking on the plus sign enclosed in a circle

* Figure 2: Dynamic reference worksheet*

- We will input the following values into
**Cell A1**in Sheet1, Sheet2, Sheet3, and Sheet4 respectively: 1000, 2000, 3000, and 4000

* Figure 3: Sheet 1 to Sheet4 containing Values in Cell A1*

- We will input the data as shown in figure 4 into the
**reference worksheet**

* Figure 4: Inputting the sheet names into the reference worksheet*

**Using a Dynamic Worksheet Reference**

**We will input the formula below into Cell C4 of the REFERENCE WORKSHEET:**

* Figure 5: How to Use a Dynamic Worksheet Reference*

- We will press
**enter**

* Figure 6: How to Use a Dynamic Worksheet Reference*

- We will use the drop-down feature to get the values for the other sheets

* Figure 7: Result of the Dynamic Worksheet Reference*

**Explanation**

The **INDIRECT** function works by evaluating text as a worksheet reference. Here, the name of each sheet is joined to the **cell reference (A1)** using concatenation:

`=INDIRECT(B4&"!A1")`

Once concatenation is done, the result is:

`=INDIRECT("Sheet1!A1")`

The INDIRECT function will recognize the value in **Cell A1** of **Sheet1** and return the value. The same applies when we use the drop-down feature for the other sheets.

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment