Copy from sheet to sheet when matches criteria

[jeremiah]

I need to copy values from Col B Sheet2, to Col D Sheet1 only if Col A on
Sheet1 matches Col A on Sheet2 and Col B on Sheet 1 has the text "Year to
Date". The last row and number of rows in between each Year to Date row will
vary each week.

 

[Bob Bridges]

How you do this depends on what you mean by "copy", jeremiah. If you mean it
literally, you can't do it with worksheet functions; you'll have to do it
with VBA, and although it isn't hard, you'll have to switch this question to
the other forum, "Excel Programming".

On the other hand, if you're content just to display it in the new location,
you can use an IF function. If, for example, when you say "Col B" etc you
really mean "this cell in col B", you can put this function in each cell of
col D: IF(AND(A2=Sheet2!A2,B2="Year to Date"),Sheet2!B2,"").

But maybe you really mean the WHOLE column - that is, you want each cell in
Sheet1!D to display the same cell in Sheet2!B only if ALL of A:A equals
Sheet2!A:A and ALL of B:B equals "Year to Date". I can think of ways to do
that, but I'll bet they're unnecessarily complex; maybe someone else can
think of a simple way to do that.

 

[jeremiah]

I actually am looking for a VBA solution. My cell locations are going to
vary every week, I can use offset for the columns but the rows are constantly
going to change. I tried to ask this request of several forums yesterday but
kept getting an error. Don't know if it was because of my internet or
something else. I did finally get it to post via the worksheet functions.
Thanks, I will ask the same question at the other forum.