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.
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('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.
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.
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.
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.
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.
➤ 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 2nd 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.
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 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.
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.