Copy cells from another workbook if vaules match

[badlydrunkboy]

Hi,

I've got a workbook which I would like to pull data into from another workbook if there is a match in two columns.

For example, if the values in cells A2 and B2 in wb1 = the value of A2 and B2 in wb2, then paste the values of C2 and D2 in wb2 into C2 and D2 in wb1.

It feels like it should be easy to do but I'm really struggling.

[alansidman]

Give this a try:

Please Login or Register  to view this content.

[badlydrunkboy]

I can't seem to get that to work I'm afraid. It doesn't pull in anything

[alansidman]

Attach your workbooks so that it can be tested. I can't help unless I see what you have and the issues. It is always beneficial to provide a sample workbook if you want to have foolproof code.

[badlydrunkboy]

Sorry, here you go. So the columns I would like filling are G and H in Confirmed Application Audit 04 (the name of this will change frequently), when the value in E and F = the values of A and B in the Replacements wb.

[alansidman]

Look at the attached.

Created a concatenation of each of the cells needed for Vlookup. Then ran vlookup to populate.

[badlydrunkboy]

Thank you, that looks great. A couple of questions though.

in the code

Please Login or Register  to view this content.

Where does the Replacements.xlsx need to be stored?

Also, is there a way of when i open another one of the Application Audit wbs, I can run a macro which will paste this formula into the right cells?

[alansidman]

Where does the Replacements.xlsx need to be stored?

It needs to be open for this action to occur. It can be stored anywhere.

Also, is there a way of when i open another one of the Application Audit wbs, I can run a macro which will paste this formula into the right cells?

The simple answer is yes, however, if you are looking for code now, you will need to be more specific as to what you want copied and where and to what worksheet.

[badlydrunkboy]

Ok, that's great. I don't suppose you happen to know if the Replacements wb could be shared do you?

The worksheet (Confirmed Application Audit xx) will always be in the same format so the formula would just need to be copied into G and H cells, all the way down, excluding and blank rows. Does that make sense?

Thank you so much for your help btw. I'm new to using excel for anything other than the most basic of tasks so I might ask silly questions and more than likely explain things badly.

[alansidman]

I don't suppose you happen to know if the Replacements wb could be shared do you?

the workbook could be set up as a shared workbook. There are some funky things that can happen when workbooks are shared. I do not happen to have the occasion to share workbooks, so I would suggest you do some research on it.

Alan

PS> If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[badlydrunkboy]

Hi, sorry about the delay in getting back to this. I've been trying to get this formula to work when I add another row in the Replacements file but the Confirmed Asset Audit file doesn't seem to update. Do you know why this might be? Also, Could you explain what each bit of the formula does because I don't quite understand it. For example, what is O3? and to my untrained eye, it looks as if exactly the same VLOOKUP is happening twice

Please Login or Register  to view this content.

Also, if i try to reference a workbook of a different name it doesn't seem to work when I change the name from Replacements.xlsx to the name of the new file.

Sorry for all the questions, like I said, I'm new to this but trying to learn.

[alansidman]

The if statement is broken down into three parts, If(criteria, result if criteria is true, result if criteria is false)
Embedded in the If statement is the criteria Iserror(Vlookup(03,[Replacements.xlsx]Sheet1!$A$2:$E$4,4,0))
The Iserror returns a true or false. It is testing whether the Vlookup is finding a result or not.
Vlookup is looking at the value in O3 and determining if it is located in the range A2:E4 in the worksheet replacements.xlsx. If it finds the value of O3 in column A, then it returns the value for that row from the 4th column. The last zero tells excel to look for an exact match.

If the criteria is true (excel did not find the value, ie a false), then excel returns an empty string, ie. ""
If the criteria returns a false value, ie. it finds a value in Column A, then return the value found.

I hope this helps you to understand and go forward.

[badlydrunkboy]

Thats excellent, thank you so much.

[badlydrunkboy]

Hi again,

I seem to be having trouble pulling some of this data through. In my attachment, you can see that the correct data has been placed in G and H when O = Windows Operating SystemXP Professional or OfficeStandard 2007 but for anything else, it doesn't populate G or H, eventhough they exist in the Replacements file.

Any ideas?

[badlydrunkboy]

Sorry, just looked at it again and noticed that it's only picking up those replacements found in A2:E4. Do you know how to change this so that it refers to all the cells with data in on that sheet?

Edit: Not to worry, I've just specified a range far bigger than I'll ever need (A1:E1000) as I couldn't work out how to look at a whole sheet.