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.

Dataset-Excel Formula 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.

cell reference-Excel Formula to Copy Text From One Cell to Another Sheet

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

inserting cell reference

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

Copy text

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

Cell reference final result-Excel Formula to Copy Text From One Cell to Another Sheet

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.


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 1st 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.

if function-Excel Formula to Copy Text From One Cell to Another Sheet

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

=IF('New York'!F13=C5,'New York'!B2:D2,"")

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.

Formula insertion

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

Outcome

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

if function result-Excel Formula to Copy Text From One Cell to Another Sheet


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.

Name box feature-Excel Formula to Copy Text From One Cell to Another Sheet

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.

Type

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

Outcome

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

Name box final outcome-Excel Formula to Copy Text From One Cell to Another Sheet


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 1st 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.

vlookup function-Excel Formula to Copy Text From One Cell to Another Sheet

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

condition

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

=IFERROR(VLOOKUP(B5,'New York'!$B$5:$F$12,2,FALSE),"")

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 2nd column (i.e., Product) data from the table_array.

FALSE = [range_lookup]

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

Formula insertion

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

Fill handle

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.


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.

copy and paste feature-Excel Formula to Copy Text From One Cell to Another Sheet

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.

Paste selection

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

Outcome

➤ 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.

Final Outcome-Excel Formula to Copy Text From One Cell to Another Sheet


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.

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo