Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Copy data from sheet 1 to sheet 2 based on the title in sheet 2

0

Hi, how can I copy the data from sheet 1 to 2 based on the title in sheet 2? 

For example

Sheet 1 

Employee ID   Name  Birth date  Gender  Grade 

123                  Peter    1-1-2000     M           1

456                  John     1-2-2000     M           2

Sheet 2

Employee ID  Birth date  Grade 

123                 1-1-2000     1

456                 1-2-2000     2

I am using vlookup in sheet 2 based on Employee ID to lookup the birth date and grade from sheet 1 to sheet 2. But, I feel really time-consuming when I have so many column titles in both sheet 1 and 2. Any shortcut to paste the data directly from sheet 1 to 2 based on the title in sheet 2? 

excel
MS Office Version: 2007
posted by: Kin on Tue Jun 9, 2020 at 4:50 am
Answer
Discuss

Discussion

If the formula is designed for deployment in all columns this shouldn't be time consuming at all. Post a [sanitized] copy of your workbook and the formulas you are now using and we'll take a look.
Variatus (rep: 4889) Jun 9, '20 at 9:03 pm
Say both sheets are started with column A and row 1. In sheet 2, the formula of Birth date for Employee 123 is =VLOOKUP($A2,Sheet1 $A$2:$E$3,3), Grade is =VLOOKUP($A2,Sheet1 $A$2:$E$3,5). The result is what i am looking for, but if there are many columns in both sheets, i have to manually adjust the formula in each column in sheet 2 to see which column in sheet 1 I look for. Not sure if there's any shortcut?
Kin Jun 10, '20 at 3:38 am
Neither am I. I need a worksheet to play around with and to test. 
Variatus (rep: 4889) Jun 10, '20 at 7:06 am
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login