PDA

View Full Version : Problem with VLookup code - Need assistance Please!!



JJFletcher
09-27-2015, 10:26 AM
I hope this is clear enough...

Rows AB27 through AC39 in the Employee_data sheet has each month listed and can have anything in there, because this is info entered by the managers during the monthly review section that the managers will perform each month... I want to show it in ReviewAgents sheet section V25 through V37.

The code currently written features data already being pulled from the Employee_data sheet ... I was trying to use the same code again to pull the data from the employee_data sheet for the comments section Rows AC28 through AC39. I started the code by making the name of the rep load up in cell O24 by entering
=INDEX(EmployeeData!A2:A421,R4)

which shows the name of the rep - I then created a comments section at Rows AB27 through AC39 and am using the code

=INDEX(EmployeeData!AB27:AC39)
so it shows up in the ReviewAgents sheet starting at location V25

14459

Hope this makes sense!

Respectfully,

John

p45cal
09-27-2015, 02:01 PM
=INDEX(EmployeeData!$AC$28:$AC$39,MATCH($AI$2,$W$4:$AH$4,0))

?

JJFletcher
09-27-2015, 08:16 PM
Hi p45cal,

Thank for the reply - the code works great but does not make available the data form each individual for their monthly employee reviews...

I have placed a sample of data that can be reviewed.. the code provided brings the data for the first rep - however data does not change with the select6ion of the new reps name by using the location $M$4 on the ReviewAgents sheet.

Would like that the selection of the month also provides the data for the correct rep. I have attached an updated sheet with the code changed - and you can see that pulling from Employee_Data sheet but unable to make available the data on reps individually showing in the location in the ReviewAgents sheet

Best Regards,

John...

PS. So close to the answer.... 14460

JJFletcher
09-28-2015, 02:31 AM
The code works great but does not make available the data from each individual for their monthly employee reviews...

I have placed a sample of data that can be reviewed.. the code provided brings the data for the first rep - however data does not change with the selection of the new reps name by using the location $M$4 on the ReviewAgents sheet. It is now reliant on the Month selection at AI2

Would like that the selection of the month also provides the data for the correct rep. You can see that pulling from EmployeeData sheet but unable to make available the data on reps individually showing in the location in the ReviewAgents sheet. Seems the code needs to include the this of each rep in order to attain the individual Results.

Best Regards,

John...

PS. So close to the answer....Link to the Workbook above in previous post.

p45cal
09-28-2015, 05:15 AM
try:
=INDEX(OFFSET(EmployeeData!$AB$27,MATCH($M$4,EmployeeData!$AB$27:$AB$500,0) ,0,12,2),MATCH($AI$2,$W$4:$AH$4,0),2)
but note that the spelling of the names in EmployeeData!AB27 and EmployeeData!AB41 etc. have to match the names in EmployeeData!A2:A23. Amy's name in A3 doesn't match her name in AB41 because the former has a trailing space.

JJFletcher
09-28-2015, 03:53 PM
Hi p45cal,

Absolutely fantastic - Question below!!!



try:
=INDEX(OFFSET(EmployeeData!$AB$27,MATCH($M$4,EmployeeData!$AB$27:$AB$500,0) ,0,12,2),MATCH($AI$2,$W$4:$AH$4,0),2)
but note that the spelling of the names in EmployeeData!AB27 and EmployeeData!AB41 etc. have to match the names in EmployeeData!A2:A23. Amy's name in A3 doesn't match her name in AB41 because the former has a trailing space.

The line formula provided is spectacular and you are awesome to say the least.... So the data is being pulled from the EmployeeData sheet - excellent... Is it possible that the Manager that is performing the one on one review with the rep can type directly into a userform to enter the data into the Months ( January through December?)... The layout will remain the same - just the number of reps will change!

Best Regards,

John

p45cal
09-28-2015, 04:09 PM
So the data is being pulled from the EmployeeData sheet - excellent... Is it possible that the Manager that is performing the one on one review with the rep can type directly into a userform to enter the data into the Months ( January through December?)... The layout will remain the same - just the number of reps will change!Yes of course, you need to follow the same logic to find which cell to write to as the formula uses to determine which cell to read from. But I'm not going to write it.