Sanjida Ahmed
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.
Look at the following image.
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.
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&” “&B16The “ “ 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.
Look at the result shown in the picture below.
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.
To find out what happened, look at the image below.
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.
Now, look at the following image to check whether it really referenced the text value Australia from Cell B7 in Cell D5 or not.
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.
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.
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:
|
| [a1] | Optional | A logical value that determines the type of reference stored in the ref_text range.
|
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.
Now, look at the following image to check what happened.
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
- [Fixed!] Relative Cell Reference Not Working in Excel
- [Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)
- How to Use FormulaR1C1 Absolute Reference in Excel VBA
- Relative and Absolute Cell Address in the Spreadsheet
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.
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.
- So, press Enter and look at the image below for the result.
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.
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.
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.
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).
- 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 SubYour code is now ready to run.
- 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.
Hello,
I have 2 things that i can’t seem to find how to do in excel.
1- How to use custom sort in a sort function?
– Ex: I have a new table with data that aggregates the values of some columns of the raw data table (different sheet), using formulas like, unique, filter, countif, etc.. As i understand it’s not possible to use the sorting methods of the filter button or Data, on the aggregation table because the cells there have formulas. Using the function sort i only have the options of ascending or descending. But one of the columns is Severity and i need to sort by High, then Medium, then Low. How would i do this?
2- How to make the contents of a cell in a column, follow another cell in the same row but on a different column, dinamicaly?
– Ex: In column A i have a list of unique names as the result of index(match(contif)) where the range is in another sheet, in the raw data table. Now in column B i want to write some observations for each name (row) in column A. The raw data sheet will change every time i collect the data. Which means, it will change the order of the names in column A. How to i make sure that the observation that i wrote the first time, remains in the same row as the name?
I appreciate your help.
Thanks, RICARDO SERRÃO for your two amazing queries. Let me help you out in solving your problems.
First, we are going to discuss your first question. The problem you faced is about custom sorting.
At the very beginning, we arranged a dataset of change in percentage.
Then, as you wished we have used the COUNTIF function in this dataset.
As a result, we have found the values.
Now, select Dataset> go to Data>Sort options.
After that, in the Sort window, select Change% in Sort By option, Cell Values in Sort On option, and Custom List in the Order option. Press OK to execute it.
Furthermore, click on the NEW LIST option and write the condition manually in the List Entries area and press Add option.
Because of that, you will get the desired condition in the Custom Lists section and press OK.
Afterward, if you can see the desired condition in the Order box then press OK.
Lastly, you will get the result accordingly. As in the condition, you have entered less than zero at first then zero to ten and at last greater than zero that’s why in the Excel section you will get the same order accordingly.
So, that’s the solution to your first query. Now, Let’s go through your second problem.
First, by reading your example, at first by using a formula, we created a Uniques Names list.
Second, you have to write down the same unique names list in another column. The behind this is, in the array, you can’t use sorting.
Third, then enter the Observations you made next to the new column.
Forth, as we want to sort, so we mark each name with a unique Id number in a new column.
Fifth, go to select the table>Data>Sort options.
Sixth, select the Id option in the Sort by option, and Cell Values in the Sort On option, click on the Largest to Smallest option in the Order section, and press OK.
Finally, you will get the desired result.
So, this is the solution to your second query.
Therefore, our journey comes to an end. The problems were very fun to solve and I really feel amazed by helping you. Thank you once again. Best of luck.