Forum Discussion
Combine data from 2 columns into 1 column
I took the following steps and had the following problem.
Steps:
1. In the 1st Excel document, data in the first two columns was combined.
2. The combined data was entered into the third column.
3. The data in the third column was highlighted and copied.
4. The data in the third column of the 1st Excel document was copied and pasted into the first column of the 2nd Excel document.
Problem:
The data did not paste. Instead, the first column of the 2nd Excel document showed "REF" throughout.
Question:
How can I pasted the data into the 2nd document without getting "REF" instead?
Thanks,
Ben
[Admin removed phone number for privacy reasons]
[Admin removed email address removed for privacy reasons]
If by "Excel document" you mean an "Excel file", you can reference data from one file to another but those files cannot be relocated or renamed once referenced without opening each file, correcting the link(s), and re-saving.
If by "Excel document" you mean "Excel worksheet", this is possible but not by using copy/paste the way you are describing.
- On the first worksheet select the cells/column that contains the combined data you want copied to the second worksheet
- On the second worksheet right click the first you want to contain your copied data and under the pop-up "Paste Options" click "Paste Link (N)"
Note how the formula for each cell references the initial worksheet and cell. Instead of doing a copy/paste you could just as easily have typed the reference formula for the first cell and then use the drag method to insert all the other references you want.
- Thanks! I had the same issue as Ben above. Paste special > Paste Link was the perfect fix!!
@bencredosforkidscom
If you want only the values and not the formula to copy over (so that the pasted content is independent of the formula), you can select the newly merged column that contains the combined info and select Copy. When you paste into the new worksheet, select Paste (V) - which will paste just the value of each cell (i.e. the merged data) without the formula so it become an independent data set.[Edit: updated who I was replying to; this answer was to Ben's question.]
