Need a forula to move data from one spreadsheet to another

[ArcticAce]

I don't know if I need to use VLOOKUP, INDEX, MATCH or a combination of them. I need to get the employees review dates from Sheet 2 into Sheet 1. The dates on Sheet 2 are in a row, and I need them on Sheet 2 in a column, but I need them in the correct month. So using Sheet 1 A3 as an example, I need the dates from Sheet 2 to populate in the correct month in Sheet 1.


I hope I explained this enough. I am good with basic excel, but now I am needing help with advance formula's.


Is there a way to do get this to work.




Sheet 1

A B C D E
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Jan Review[/TD]
[TD]Feb Review[/TD]
[TD]Mar Review[/TD]
[TD]Apr Review[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD]1/5/2018[/TD]
[TD]2/4/2018[/TD]
[TD]3/3/2018[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2

A B

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Review Conducted On[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]3/2/2018[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[TD]1/2/2018[/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD]2/4/2018[/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD]3/3/2018[/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD]1/5/2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[ArcticAce]

Can anyone help me with the formula?

 

[Sam_ExcelVault]

Is there a reference to the review month on Sheet2?

If Sheet2 was set up with Employee, Review Month, Review Conducted On as header in day cells A1 to C1 you could you MATCH & INDEX to return the Review Conducted On date for each Employee - Review Month combination.

 

[ArcticAce]

Sam_ExcelVault, so I need to add a Review Month Column? The data in sheet to comes from an automated report, but I can add the column. What would the formula be to get it MATCH & INDEX, also transposed.

 

[Sam_ExcelVault]

I would add a "helper column" onto Sheet2 and use the EOMONTH function in that cell to return the last date of the month for each date in the "Review Conducted On" column. So in your above example, on Sheet2 in Cell C2 you'd have:

=EOMONTH(B2,0)

Then set up your Sheet1 to have the last day of the month in Row1 for each Review Month e.g. Jan Review = 31/01/2018, Feb Review = 28/02/2018. You can use a customer data format to have Excel display Jan Review etc. rather than a date.

Then use this array formula in Cell B2 and drag across for each review month and down for each employee in your list:

=IFERROR(INDEX(Sheet2!$A$1:$C$6,MATCH(Sheet1!$A$2&Sheet1!B$1,Sheet2!$A:$A&Sheet2!$C:$C,0),2),"")

Make sure you commit the formula by pressing CTRL + SHIFT + RETURN to insert as an array formula. It uses the MATCH function to find the particular row that has the employee name and corresponding review month and then returns the actual review date. IFERROR is wrapped around the formula to return a blank value if there isn't a review date for the given employee/month combination.

I hope that makes sense/helps!

 

[ArcticAce]

Sam_ExcelVault, thank you very much it worked for me. I really appreciated your help. Now, I have one more question, can I do this in a table. If so, can you help me.

​