Copy values from one sheet to another sheet

August 24, 2012 at 10:23:50
Specs: Windows 7
I have 2 sheets, both sheets have an ID column. I want to copy a column's value from sheet 2 to sheet 1 if the ID column matches between the 2 sheets. For example, I have ID 36 in column A on both sheets. I want the cell from column E from sheet 2 to copy to Sheet 1's column d if column A matches on both sheets. I hope this makes sense. I prefer to just use a formula. Thanks to any who replies.

MK


See More: Copy values from one sheet to another sheet

Report •


#1
Report •

#2
August 24, 2012 at 10:47:59
No...my brain is shot today and I cant figure out the formula to copy from one sheet to the other using vlookup. I don't have a static # to lookup in the formula like the example states in Office help. I want to copy from one sheet to another, the value in sheet2, Column E(all cells) that matches column A on both sheets. I am sorry if this doesn't make much sense. I seems like it should be such an easy thing but I can't wrap my brain around it. Basically I am manipulting data to create an import file for an sql database. If this was sql I would be done by now-funny huh.
Thanks.

Report •

#3
August 24, 2012 at 11:31:01
Since I can't see your spreadsheet from where I'm sitting, I'll have to make some assumptions.

In Sheet 1, you have this:

        A     B     C     D
1      ID                Data
2      36
3      37
4      38

In Sheet2, you have this:

        A     B     C    D     E
1      ID                     Data
2      38                      Y
3      36                      X
4      37                      Z

In Sheet1!D2 enter this formula and drag it down:

=VLOOKUP(A2,SHEET2!$A$2:$E$4,5,0)

This should look up the values from Sheet1!Column A in Sheet2!Column A and return the corresponding value from Sheet 2 Column E.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 24, 2012 at 12:41:16
That looks like what I need but what if there are cells on sheet 2 with no data? In other words there are ID's on one sheet but not on the other...will that cause an error? i have posted an example below...formatting isn't pretty but I think you get the jist.
value from column c from sheet 2 goes to column c on sheet 1 where A=A


Report •

#5
August 24, 2012 at 12:50:19
 
SHEET1:
A       B                      C
ID	IssueAmount    UsedAmount
1	$35.00	
14	$50.00	
53	$5.00	
56	$10.00	
57	$10.00	
58	$10.00	

SHEET 2:
A      B                      C
ID	RedeemAmount	     TotalUsed
1	$12.00	
1	$4.00	
1	$2.22	
1	$4.26	              22.48
14	$3.73	
14	$3.20	
14	$4.53	
14	$3.73	
14	$1.57	
14	$2.50	
14	$3.20	
14	$6.38	
14	$1.86	
14	$1.86	
14	$4.37	
14	$1.60	
14	$3.20	
14	$2.93	
14	$3.20	
14	$0.30	               48.16
53	$4.50	               4.50
56	$2.40	
56	$1.65	
56	$3.14	               7.19
57	$3.99	
57	$4.26	
57	$1.70	               9.95



Report •

#6
August 26, 2012 at 06:49:31
Errors produced by VLOOKUP can be handled using an IF function to check for the error.

If VLOOKUP can't find the value it is looking for, it will return #N/A. To eliminate the error, you would use the ISNA function to check for the error and return something else, such as a text string. Something like this, which has not been tested:

=IF(ISNA(VLOOKUP(A2,SHEET2!$A$2:$E$4,5,0)),"Not Found",=(A2,SHEET2!$A$2:$E$4,5,0))

However, VLOOKUP only finds the first occurrence of the lookup_value but you appear to have multiple matching values in Sheet 2.

Perhaps you could create a column of subtotals for each ID and then use on that column to import the data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question