I need to copy cells from one sheet to the other based on another cell.
If cell.columnA from sheet1 contains the text from cell.columnA from sheet2, copy cell.columnB, cell.columnC, cell.columnD from sheet1 to sheet2.
Is this possible to do?
-
This is possible, both via macros ("on demand") and formulas ("immediately"). If you go with formulas, a simple IF-formula will do, just read into that and come back if anything is unclear. For macros it will be a bit more complex (though not too difficult for a beginner). First you need to decide what you want, then come back to ask more specific questions.sina– sina2013-12-12 14:04:36 +00:00Commented Dec 12, 2013 at 14:04
Add a comment
|
1 Answer
This is completely possible. I would use the if and vlookup functions. Something like this:
=IF($A1=Sheet1!$A1, VLOOKUP(Sheet1!$A1, Sheet1!$A1:$D1, 2),"")
The dollar signs lock the columns so you can paste over many rows, the if checks for equality of column a values, Sheet1!$A1:$D1 is the range you specified between columns a through d, and the 2 represents the second column in that range. This example was for column b in row 1 on sheet 2.
9 Comments
sina
vlookup is not even neccessary.
C1pher
I would prefer to change which column in a range I'm in. There are other ways it can be calculated. This one feels cleaner to me.
user3095635
Sorry, I am very basic with this, please be patient. How does it know to compare to the different sheets with that if statement?
C1pher
Because the formula is in sheet 2, it defaults to sheet 2 if you don't say otherwise. So the first part of the if is the condition.
$A1=Sheet1!$A1 is saying A1 from Sheet2 is equal to A1 from Sheet1? If it it true, then it calculates the second part.C1pher
The second part of the if only calculates if the first part is true.
vlookup' may not be the simplest way, but it looks for a value Sheet1!$A1` in a range Sheet1!$A1:$D1 and when it finds a match, will go to whichever column you specify to pull the value. In this example, colB=2, colC=3, colD=4. |