Want to use Excel to **reference cells** in another sheet **based on a cell value**? In this tutorial, I will explain the whole process step by step. I will basically show two ways of doing so. But before going into the discussion, I want to review your memory with the ** Excel INDIRECT function**.

## Download Excel Workbook

First, download the working Excel file that I have used to write this article.

## Excel INDIRECT Function

Excel **INDIRECT function **returns the reference specified by a text string.

The syntax of **the INDIRECT function**:

`=INDIRECT(ref_text, [a1])`

Here,

**ref_text (required)**: This argument can take any of the following inputs:- A cell reference of A1-style. For example,
,*INDIRECT(A1)*,*INDIRECT(B2)*, etc.*INDIRECT(D100)* - An R1C1-style reference. For example, INDIRECT(R9C7), INDIRECT(R2C3), etc.

- A cell reference of A1-style. For example,

**Notes:** To use this reference, you have to activate the R1C1 reference from the ** File **>

**>**

*Options***>**

*Excel Options***>**

*Formulas***> Check**

*Working with Formulas*

*R1C1 reference style*Use defined names as references. For example, =INDIRECT(“old_value”), =INDIRECT(“new_value”) where old_value = A5 and new_value=B5.

A reference to a cell as a text string. For example, INDIRECT(“A1”), INDIRECT(“D15”)

**a1 (optional)**:- If a1 is omitted, or 1, the cell reference is of type A1.
- If it is false, it refers to cell reference R1C1.

In the following image, you can see some uses of the Excel *INDIRECT***function**.

Now let’s discuss how to use Excel to reference a cell in another worksheet based on cell value.

## 2 Examples for Reference Cell in Another Excel Sheet Based on Cell Value

I will demonstrate how to reference a cell in another Excel sheet using **the INDIRECT** **function** with the data validation list for the first method. Then, for the last method, I will use **the INDIRECT** **function** with the cell references. For both of these methods, I will also implement the ampersand operator in the formula.

### Example 1: Select a Single Cell and Refer to a Whole Range of Cells

I have two Excel worksheets with the names **AAPL **and **MSFT**. You can have many. Both worksheets have similar kinds of data. **Profit (PCO)**, **EPS**, and **Growth **of two companies for the last 5 years.

What I want is: in the **Main **worksheet, I will input the company name from a drop-down and all those values (**Year**, **PCO**, **EPS**, and **Growth**) will be shown in the **Main **worksheet.

This method can be helpful if you’re maintaining the same kind of data across hundreds of worksheets. It is toilsome to search for every relevant worksheet and then view the data. For simplicity, I am using just two worksheets. I shall use a formula like this:

`=INDIRECT('worksheet_name'!column_reference&row_reference)`

**Steps:**

- For
, I have taken the help of this cell range:*column_reference*. This range holds values from*C11:G11***A**to**E**. You can extend it as per your necessities. For, I have taken the help of this cell range:*row_reference*. This range holds values from 5 to 13. Extend it as per your necessities.*B12:B16* - This is the real formula that I have used in cell
of worksheet Main:*B5*

`=INDIRECT("'"&$H$6&"'!"&D$11&$B12)`

- Now,
**apply this formula to other cells**in the range ().*B5:E9*

- This is the result you will get:

**Formula Breakdown**

- This part of the formula
**(“‘”&$H$6&”‘!”)**returns the worksheet name**“MSFT!'”**(for the above image). - This part
**&D$11**refers to cell reference**D11**and returns the text value**&”B”**. - And this part,
**&$B12**refers to cell reference**B12**and returns the numeric value**&5**. - So, this is the overall return from the 3 parts of the formula:
**“‘MSFT!'”&”B”&5**=**“‘MSFT!’B5”**

**Notes:** When a text value and a numeric value are concatenated in Excel, the return is a text value.

- And here is the ultimate return of the formula:
**INDIRECT(“MSFT!’B5”)** - When I apply the formula to other cells in the range, it works because we have used mixed cell references like
and*D$11*. When the formula is applied to the cells on the right, only the column references change, for reference*$B12*. When the formula is applied to the cells on the bottom, only row references change for reference*D$11***$B12**.

**Notes:** To see how the different parts of an Excel formula work, select that part and press the * F9* key. You will see the value of that part of the formula.

**Read More:** **How to Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)**

**Similar Articles**

**How to Link Excel Sheets to Another Sheet (5 Ways)****Link Files in Excel (5 Different Approaches)****How to Link Cell to Another Sheet in Excel (7 Methods)****Link Sheets in Excel with a Formula (4 Methods)**

### Example 2: Reference Individual Cell of Another Worksheet

In this example, I am pulling a row from another worksheet based on some cell values (references).

**Steps:**

- Firstly, I have created the following dataset.
- Secondly, I have typed this formula in cell
.*C10*

`=$C$4`

- Thirdly, type another formula in cell
.*C11*

`=INDIRECT("'"&$C$4&"'!"&$C5)`

- Then,
for the cell range*AutoFill the formula*.*C11:C14*

- After that, it will look like this.

I am showing this method as somebody might find it useful in his job. If you want to go to another company for another year, you have to change 5 values. Check out this image now.

I will not explain how this formula works, as these formulas are the same as the above one.

**Read More:** **How to Link Data in Excel from One Sheet to Another (4 Ways)**

**Conclusion**

I have shown you how to use **Excel** to **reference cells** in **another sheet **based on a **cell value**. I hope this helps you out when you want to show data from a lot of worksheets in the main worksheet. Do you use any better methods? Let me know in the comment box.

**Further Readings**

**How to Link Data from One Spreadsheet to Another in Excel****Reference from Another Excel Workbook without Opening (5 Examples)****How to Link Sheets to a Master Sheet in Excel (5 Ways)****Transfer Specific Data from One Worksheet to Another for Reports****How to Link Two Sheets in Excel (3 Ways)****Reference Worksheet Name in Formula in Excel (3 Easy Ways)**

Great Article!!! I can put this to use right now 🙂 It would be even more powerful if I knew how to pull a table of data in from another worksheet or, even better, another workbook without the cell reference table being used as reference… For example I have similarly formatted data that I want to chart using the drop down as you have already done. To have this reference table, even hidden, will distract the people I will present the data to…

Thanks for your feedback, JG. I will think about a method as per your necessity.

Best regards

Kawser

when a cell has a number (12345) that is ok but when the next cell has($123.45) how to get the decimal in

with out having to manual installing it.when there is ten cells part are numbers part are dollars.

i guess i will just put them ?

a b c d e

12 2 24 $1.99 $4.39

like this

jr

=SUM(sheetname1!cell reference, sheetname2!cell reference,…)