How to Reference Text in Another Cell in Excel (14 Ways)

When we are dealing with a large amount of data in Excel, sometimes we want to cite/reference certain texts from one cell to another for better understanding. In this article, we will show you how to reference text in another cell in Excel.


Download Workbook

You can download the free practice Excel workbook from here.


14 Ways to Reference Text in Another Cell in Excel

In this section, you will learn 14 different effective ways to reference text in another cell with formulas, Excel tools and VBA code.

1. Reference Text from One Cell to Another Cell in the Same Worksheet in Excel

Consider the following example. What we are going to do with it is, we will take Cell B7 and reference the text, Australia, from it in another Cell D5.

The steps to do that are given below.

Steps:

  • In Cell D5, write an equal sign (=).
  • Following that, write the cell reference number, B7, that you want to cite in Cell D5.

The formula will be like this:

=B7

  • Then, press Enter.

Reference Text from One Cell to Another Cell in the Same Worksheet in Excel

Look at the following image.

Result of Reference Text from One Cell to Another Cell in the Same Worksheet in Excel

As a result, Cell D5 is holding the text value, Australia from Cell B7.

Read More: Reference Another Sheet in Excel (3 Methods)


2. Citing Text from a Cell to Another Cell in Different Worksheet in Excel

We have a list in the worksheet named Dataset (shown in the picture below).

What we want to do here is, we will refer to a specific text, Australia from Cell B7, in Cell C5 of the worksheet named Reference (shown in the picture below).

Steps:

  • In Cell C5 of the Reference worksheet, write an equal sign (=).

  • Following that, click on the Dataset sheet and select B7, the cell that you want to cite.

The formula will be like this:

=Dataset!B7
  • Then, press Enter.

Look at the result shown in the picture below.

Reference Text from One Cell to Another Cell in the Different Worksheet in Excel

The text value Australia from Cell B7 in the Dataset sheet is now cited in Cell C5 in the Reference sheet.

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


3. Referencing and Concatenating Texts in Another Cell

You can also concatenate while referencing texts in another cell. The steps on how to do that are given below.

For this article, we will reference and concatenate Cells B7, B12 and B16 in Cell D5.

Steps:

  • In Cell D5, write an equal sign (=).
  • Following that, write the cell reference numbers with ampersand symbol (&) between them.

The formula will be like this,

=B7&” “&B12&” “&B16

The “ “ in between the ampersand symbol (&) is for spacing out among the referenced texts in Cell D5. You can omit this if you don’t want to put space between texts.

  • Then, press Enter.

Excel reference and concatenate text in another cell

Look at the result shown in the picture below.

Result of excel reference and concatenate text in another cell

The text values Australia, Bangladesh and Ireland are referenced together through the corresponding Cells B7, B12 and B16 in Cell D5.

Read More: How to Find and Replace Cell Reference in Excel Formula


4. Reference a Range of Texts in Another Cell in Excel

Till now, we have been referencing text from a single cell. What if we want to reference a range of texts from different cells in other cells.

Let’s see how to do that in Excel.

Steps:

  • In Cell D5, write an equal sign (=).
  • Following that, select the first cell from the range that you want to reference text from and drag through the last cell of the range. For instance, we wanted to reference text value from Australia (in Cell B7) to Afganistan (in Cell B13) in Cell D5, so we select Cell B7 first and drag it till Cell B13.

In Cell D5, the formula looks like this:

=B7:B13
  • Then, press Enter.

Reference a Range of Text in Another Cell in Excel

To find out what happened, look at the image below.

Result of Reference a Range of Texts in Another Cell in Excel

Finally, we get a range of texts – Australia to Afganistan – in Cell D5 through corresponding Cells B7 to Cell B13 by referencing.

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


5. Cite Text in Another Cell with Named Range in Excel

If you don’t want to reference a text by its corresponding cell reference number, then you can utilize the Named Range option of Excel.

In this section, we will show you how to cite a text through Named Range.

Steps:

  • First, select the cell that you want to cite. In our case, it is Cell B7.
  • Second, right-click on the selected cell. A list of options will appear.
  • Third, select Define Name… from the options.

  • After that, a New Name pop-up box will appear. In the Name box, write any name that you like (we named our cell Aussie).
  • Later, click OK.

  • We have successfully named the Cell B7 Aussie (shown in the picture below).

  • After that, in Cell D5, write an equal sign (=), following the newly defined name (Aussie).

The formula in Cell D5 will be like this:

=Aussie
  • Then, press Enter.

Reference Text in Another Cell in Excel with Named range

Now, look at the following image to check whether it really referenced the text value Australia from Cell B7 in Cell D5 or not.

Result of Reference Text in Another Cell in Excel with Named range

As we can see from the picture above, Named Range can successfully reference text from one cell to another.

Read More: Excel VBA: Get Cell Value from Another Workbook without Opening


6. Reference Multiple Texts with Named Range in Another Cell

In the previous section, we have learned how to reference a single cell by defining a name. And in this section, you will learn how to cite a range of cells with Named Range in Excel.

Steps:

  • First, select the range of cells that you want to cite. In our case, it is from Cell B7 to B13.
  • Second, right-click on the selected cells. A list of options will appear.
  • Third, select Define Name… from the options.

  • After that, a New Name pop-up box will appear. In the Name box, write any name that you like (we named our range Countries).
  • Later, click OK.

  • We have successfully named the range B7:B13 Countries (shown in the picture below).

  • After that, in Cell D5, write an equal sign (=), following the newly defined name (Countries).

The formula in Cell D5 will be like this:

=Countries
  • Then, press Enter.

Result of Reference Range of Text in Another Cell in Excel with Named range

Now, look at the following image to check whether it really referenced the text value from Australia to Afganistan from Cell B7 to B13 in Cell D5 or not.

Result of Reference Range of Text in Another Cell in Excel with Named range

As we can see from the picture above, Named Range can successfully reference a range of texts from one range to another.

Read More: Example of Relative Cell Reference in Excel (3 Criteria)


7. Reference Text from a Cell to Another Cell in the Same Worksheet with the INDIRECT Function

The INDIRECT function in Excel is used to return the reference specified by a text string.

  • Syntax:
=INDIRECT(ref_text, [a1])

  • Return Value:

Returns the reference value specified by a text string.

  • Parameter Description:
Parameter Required/ Optional Description
ref_text Required A reference of a cell containing an A1-style reference, an R1C1-style reference, a name defined as a reference, a text string as a reference.

The INDIRECT function will return the #REF! Error, if:

  • ref_text does not hold a valid reference.
  • the source workbook is not open. If ref_text refers to a different external reference workbook, then the other workbook must be open.
  • ref_text indicates a range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD).
[a1] Optional A logical value that determines the type of reference stored in the ref_text range.
  • If TRUE or omitted, ref_text is generated as an A1-style reference.
  • If FALSE, ref_text is generated as an R1C1-style reference.

 

Following from here, you will learn how to utilize the INDIRECT function to reference a text from one cell to another.

In section 1, we have learned how to execute a formula to reference a text from one cell in another. But this time, we will learn how to do that with INDIRECT. We will take Cell B7 and reference the text, Australia, from it in another Cell D8 with the INDIRECT function.

Steps:

  • In Cell D8, write an equal sign (=), the function name, INDIRECT and pass the cell reference number that you want to cite the text from inside double quotes (“”).

The formula becomes:

=INDIRECT(“B7”)
  • Then, press Enter.

Reference Text from One Cell to Another Cell in the Same Worksheet in Excel with Indirect

Now, look at the following image to check what happened.

Result of Reference Text from One Cell to Another Cell in the Same Worksheet in Excel with Indirect

The INDIRECT function can successfully reference the text Australia from Cell B7 in Cell D8.

Read More: Excel Sheet Name in Formula Dynamic (3 Approaches)


Similar Readings


8. INDIRECT Function to Reference Text from a Cell to Another Cell in Different Worksheet

We have seen in section 2 how to reference a text from one sheet to another cell in another sheet. In this section, we will see how to do that with the INDIRECT function.

We have a list in the worksheet named Dataset (shown in section 2) What we want to do is, we will refer to a specific text, Australia from Cell B7, in Cell C5 of the worksheet named Reference (shown in section 2).

Let’s see how to do that with INDIRECT.

Steps:

  • In Cell C8 of the Reference worksheet, write an equal sign (=), the function name, INDIRECT and click on the Dataset
  • Then, select B7, the cell that you want to cite the text from. Don’t forget to write everything inside the parentheses in double quotes (“”).

The formula in Cell C8 of the Reference worksheet will look like this:

=INDIRECT(“Dataset!B7”)
  • Then, press Enter.

Reference Text from One Cell to Another Cell in the Different Worksheet in Excel with Indirect

As you can see from the picture above, the text value Australia in Cell B7 of the Dataset sheet is now cited in Cell C5 in the Reference sheet.

Read More: Different Types of Cell References in Excel (With Examples)


9. Referencing and Concatenating Texts in Another Cell with the INDIRECT Function

Referencing and concatenating with the INDIRECT is a bit tricky. To understand this process, read this article carefully.

Look at the following example. In Cell D8, do as you already know that after an equal sign (=) we have to write INDIRECT and pass the reference inside the parentheses inside double quotes (“”). Now look closely at how we passed the reference here.

  • First, we wrote “B” inside double-quotes (“”). It means we are passing Column B.
  • Second, as we are concatenating here, so we put an ampersand symbol (&).
  • Third, we passed 7. It means we are passing row number 7.
  • Now, the whole procedure means is, we are concatenating Column B and Row 7 inside the INDIRECT
  • Cell B7 carries Australia. So, let’s see after pressing Enter, Cell D8 really reference the text from B7 or not.

Excel reference and concatenate text in another cell with Indirect

  • So, press Enter and look at the image below for the result.

Result of Excel reference and concatenate text in another cell with Indirect

As a result, Cell D8 is now carrying the referenced text from Cell B7, Australia with the help of the INDIRECT function.

Read More: Cell Reference in Excel VBA (8 Examples)


10. INDIRECT Function to Reference a Range of Texts in Another Cell in Excel

It is also possible to reference a range of texts from different cells in other cells with INDIRECT.

Let’s see how to do that in Excel.

Steps:

  • In Cell F5, write an equal sign (=), the function name, INDIRECT and pass the range of cell reference numbers that you want to cite the text from inside double quotes (“”).
  • The above step means, inside the double quotes (“”) of the parentheses, select the first cell from the range that you want to reference the text from and drag through the last cell of the range. For instance, we wanted to reference text value from Australia (in Cell B7) to Afganistan (in Cell B13) in Cell F5, so we select Cell B7 first and drag it till Cell B13.

In Cell F5, the formula becomes:

=INDIRECT(“B7:B13”)
  • Then, press Enter.

Reference a Range of Text in Another Cell in Excel with Indirect

Consequently, you can see from the above image that we get a range of texts – Australia to Afganistan – in Cell F5 to F11 through corresponding Cells B7 to Cell B13 by referencing with INDIRECT.

Read More: How to Keep a Cell Fixed in Excel Formula (4 Easy Ways)


11. INDIRECT Function to Cite Text in Another Cell with Named Range in Excel

In above section 5, you have learned how to reference text by Named Range with formula. You can execute the same task with the INDIRECT function in Excel.

Steps:

  • As shown in section 5, select the cell (B7) that you want to cite the text from and right-click on it.
  • From the appeared option list, select Define Name…
  • Then, insert any name that you want the cell to have (we named the cell Aussie).
  • Later, click OK.
  • After that, in Cell D8, run an INDIRECT function with the defined name (Aussie) inside double quotes (“”) of the parentheses.

The formula will look like this:

=INDIRECT(“Aussie”)
  • Lastly, press Enter.

Reference Text in Another Cell in Excel with Named range with Indirect

The picture shown above tells us that the text value Australia from Cell B7 is successfully referenced in Cell D8. So now we know that Named Range can successfully reference text from one cell to another with the INDIRECT function.


12. Reference Multiple Texts with Named Range in Another Cell with the INDIRECT Function

In above section 6, you have learned how to reference multiple texts by Named Range with formula. You can execute the same task with the INDIRECT function in Excel.

Steps:

  • As shown in section 6, select the range of cells that you want to cite and right-click on it. In our case, it is from Cell B7 to B13.
  • From the appeared option list, select Define Name…
  • Then insert any name that you want the cells to have (we named the cell Countries).
  • Later, click OK.
  • After that, in Cell D8, run an INDIRECT function with the defined name (Countries) inside double quotes (“”) of the parentheses.

The formula will look like this:

=INDIRECT(“Countries”)
  • Lastly, press Enter.

Reference Range of Text in Another Cell in Excel with Named range with Indirect

The picture shown above tells us that the text values from Australia to Afganistan from Cell B7 to B13 are successfully referenced in Cell F5:F11. So now we know that Named Range can successfully reference multiple texts from one range of cells to another with the INDIRECT function.


13. Reference Text from a Cell to Another Cell with Paste Special in Excel

This time we will learn how to utilize Excel’s Paste Special tool to reference text from one cell to another. The steps to do that are shown below.

Steps:

  • First, select the cell that you want to cite the text form (in our case, it is Cell B7).
  • Then, right-click onto the cell. A list of options will appear.
  • From there, select Copy from the options or you can press Ctrl + C from your keyboard to copy a selected cell.
  • Cell B7 is copied now.

  • Now, go to another cell where you want to reference the text Australia from Cell B7. We picked Cell D5 to store that.
  • Right-click onto the cell (D5) and from the appeared list, select Paste Link (N).

Excel reference text in another cell with paste special

  • This will paste the reference value of the copied cell in the target cell. Look at the following image where Cell D5 is holding the text value Australia of the cell reference B7 ($B$7 shown in the formula bar).

Now consider the following gif.

Every time you change the value in Cell B7, the result Cell D5 will automatically be updated according to the text reference.


14. Embed VBA to Reference Text from a Cell to Another Cell in Excel

If you are an advanced Excel user, then this section is for you. Because you can implement VBA code to reference text in another cell. Implementing VBA is the most effective, quickest and safest method to run any operation in Excel.

Here, we will see how to auto-update the reference every time the source data is changed with VBA code in Excel. Meaning, if we change the text value of Cell B7 from Australia to Canada, the result Cell D5 will automatically be updated.

Let’s see how to do that with VBA macro.

Steps:

  • In the beginning, right-click on the sheet where you want to implement the referencing.
  • Then, select View Code from the appeared list. As shown below, we right-clicked on the sheet named VBA where our dataset is stored and selected View Code from the options.

  • Then, copy the following code and paste it into the auto-generated code window.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Worksheets("VBA")
        If .Range("B7").Value <> "" Then
            On Error Resume Next
            Range("B7").Copy (.Range("D5"))
        End If
    End With
End Sub

Your code is now ready to run.

VBA excel reference text in another cell

  • Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the code execution, look at the gif below to see the result.

After the successful code execution, when we changed the text value in Cell B7 from Australia to Canada, the result Cell D5 is automatically be updated.

Read More: Example of Mixed Cell Reference in Excel (3 Types)


Conclusion

To conclude, this article showed you 14 effective methods on how to reference text in another cell in Excel. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo