Excel Formula to Copy Text from One Cell to Another Sheet

Often, we have to deal with multiple worksheets in Excel. Therefore, we roam around the sheets to look for an entry or value. Excel formula to copy text from one cell to another sheet is one of many solutions to avoid this kind of setback.

Let’s say, we have Sale data for December’21 of three different cities; New York, Boston, and Los Angeles. These 3 Sale data are identical in orientation, so, we show only one worksheet as a dataset. In this article, we demonstrate ways to copy text from one cell to another sheet.

Download Excel Workbook

5 Easy Excel Formulas to Copy Text from One Cell to Another Sheet

Method 1: Cell Reference to Copy Text from One Cell to Another Sheet

Excel allows us to insert a text from one cell to another worksheet using cell references. For instance, we want the city name (i.e., New York, Boston, or Los Angeles) in another sheet beside their Total Sale value.

Step 1: To assign the city name (i.e., text) to a cell value (i.e., C6), type an Equal sign (=) in any adjacent cell (i.e., B6). Then, click on the respective sheet (i.e., New York) from the Sheet Name Ribbon.

Step 2: Click on the Sheet Title and Excel shows the formula in the Formula bar. Press ENTER.

➤ Pressing ENTER assigns the text to the adjacent value as depicted in the following image.

Step 3: Repeat Steps 1 and 2, afterward you achieve the same scenario as shown below.

For better understanding, we use multiple worksheets’ single cells to copy a text from. However, copying cell texts from a single worksheet just need one execution of Steps 1 and 2. After that, you just drag the Fill Handle to copy the rest if you desire.

Read More: How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)

Method 2: Use of IF Formula to Copy Text from One Cell to Another Sheet

What if we want to copy the text depending on a condition? For example, we want a formula that 1^st compares the Total Sale amount of each city to a value entered in another sheet. Then, if the entered value equals the Total Sale amount, the formula copies the dataset Title. The copied dataset Title is assigned to the entered value. To make this happen, we can use the IF function.

Before moving to the procedure, let’s see what we want to achieve from the following dataset. In the dataset, we have the Total Sale amount (i.e., $1589.10), Cell Reference (i.e., F13), and the dataset Title (i.e., B2:D2). We want the dataset Title text if the entered cell value is equal to the Total Sale amount.

Step 1: Paste the following formula in any blank cell (i.e., B5).

In the formula, the IF function performs a logical_test (i.e., ‘New York’!F13=C5). If the value in C5 equals the New York city Total Sale amount, the formula inserts the dataset Title. Otherwise, the C5 cell remains empty.

Step 2: Hit ENTER to copy the cell text in another cell entry.

➤ Follow Steps 1 and 2 to display the dataset Title Texts assigning the Total Sale amounts.

Read More: Excel Formula to Copy Cell Value from Another Sheet

Similar Readings

• All Paste Options in Excel with Time Saving Shortcuts
• How to Copy Rows in Excel (4 Easy Ways)
• Excel VBA to Copy Only Values to Destination (Macro, UDF, and UserForm)
• PasteSpecial Method of Worksheet Class Failed (Reasons & Solutions)
• Excel VBA: Copy Range to Another Workbook

Method 3: Name Box to Insert One Cell Text in Another Sheet

In previous methods, we used Excel features to copy text from one cell to another sheet. Similarly, Excel’s Name Box feature does the same job.

Step 1: Click on the dataset Title then move the cursor to the box (i.e., Name Box) beside the Formula Bar as shown in the below screenshot.

Type a suitable name for the dataset Title and hit ENTER.

Step 2: Return to the destined sheet or any other sheet where you want the assigned name text.

Inside that sheet, type =New.. in the respective cell. Instantly, you see an assigned text NewYork.

Double Click on it.

➤ Double Clicking on NewYork pastes the assigned text in cell B5.

➤ Repeating Steps 1 and 2 leads to a similar depiction like the screenshot below.

Read More: How to Copy a Cell in Excel Using Formula(7 Methods)

Method 4: Excel VLOOKUP Formula to Copy Text from One Cell to Another Sheet

In all previous methods, we copy one single cell text to another sheet. In those cases, we demonstrate coping single cells from different worksheets then pasting them in a new worksheet. But in this method, we describe how to copy multiple single cells from a worksheet depending on a condition to another worksheet.

The VLOOKUP is an effective function to copy texts from a worksheet to another sheet. The syntax of the VLOOKUP function is

=VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])

lookup_value; value to look for in the 1^st column in the table_array.

table_array; table or range where the values reside.

column_index_num; column number that sits values that are about to be copied.

[range_lookup]; [Optional] by default TRUE (approximately matched) or FALSE (exact match)

Let’s say, we want the Product Names copied depending on their order dates in another sheet.

➤ In another sheet, we have some order dates and we copy the Product Name text that matches the order dates.

Step 1: Write the following formula in the C5 cell.

Comparing the formula with the VLOOKUP syntax results

B5 = lookup_value

‘New York’!$B$5:$F$12 = table_array, sheet range reference from where we copy the texts.

2 = column_index_num, we want 2^nd column (i.e., Product) data from the table_array.

FALSE = [range_lookup]

The IFERROR function inserts an empty cell whenever any error happens.

Step 2: Use the ENTER key then Drag the Fill Handle to display all cell texts that match order dates.

In this method, we copy multiple single cells to another sheet. This method is handy when we need to fetch data from a dataset maintaining a condition.

Read More: Excel VBA to Copy Rows to Another Worksheet Based on Criteria

Method 5: Copy and Paste Feature to Insert Texts in Another Sheet

Though this is not any conventional formula, still we’re providing this for your ease of work. In case, if we have a handful of cell texts to copy to another sheet, we can use Excel Copy and Paste feature. Excel’s Paste Special offers the Paste Link option in order to paste text in another sheet from one cell.

Step 1: Right Click on the cell whose text you want to copy. The Context Menu appears. From the Context Menu, Select Copy.

Step 2: Now, go to the destined sheet (i.e., Copy and Paste). Again, Right Click on the respective cell (i.e., B5) adjacent to its assigned value (C5). The Context Menu appears. Select Paste Link under the Paste Options.

➤ In a moment, the copied cell text appears in the respective cell.

➤ Execute the Step 1 and 2 to copy the cell texts in the destined cell in another sheet. After that, the other sheet gets filled with copied texts.

Read More: Macro to Copy Specific Columns from One Worksheet to Another in Excel

Conclusion

In this article, we use Excel functions and features in formulas to copy text from one cell to another sheet. Cell Reference, Copy and Paste, and Name Box features are convenient in making an Excel formula to copy text from one cell to another sheet. Excel IF and VLOOKUP functions also do the job. Hope these above-mentioned methods work in a way that fulfills your need. Comment if you have further inquiries or have anything to add.

Related Articles

• VBA Code to Compare Two Excel Sheets and Copy Differences
• [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
• Difference Between Paste and Paste Special in Excel
• How to Copy Alternate Rows in Excel (4 Ways)
• Copy and Paste in Excel and Keep Cell Size (7 Examples)
• [Fixed!] Cannot Copy Merged Cells in Excel (4 Solutions)
• How to Copy and Paste in Excel When Filter Is On (5 Methods)