Need help with a form. to find a value and then display a cell that is on a diff row & col

[CTGuyton]

Hello. I'm brand new here. I found this site through a Google search trying to look up ways to solve my problem.

I'm a clerk for a company, and my job is basically to take the punch in and out times from one system, and input them into another. It's a little more complicated, but that's the part I'm trying to work on today. The first hour to hour and a half of my job, I'm basically just writing the times from one print out onto another to make it easier to reference them as I'm inputting all this data. If I could just copy and paste it into excel and have it pull out the times and put them in the correct order for me, that would save me at least an hour every night.

I'm not even sure if what I'm trying to do is possible with excel. I've tried lookup and if, and dabbled with index-match but I can't seem to come up with something that does what I need it to.

Basically, I would like to copy and paste the report on sheet 1. Then on Sheet 2, I've got my list of employees sorted how I need them to be.

I'd like to have a formula that checks the list in Sheet 2 Col A(Employees) and find their name in Column D on sheet 1(Report)
Then I'd like to be able to put in Columns B C D E their punch in , lunch out, lunch in, and punch out times.
Let's same the Employee name is D1 on sheet 1. The times I need are E3, H3, E4, H4. So they're not in the same row or column or even in a straight line.

Is something like this even possible? I would really appreciate any help you guys could give me. And if anyone would like a copy of my workbook I'd be happy to send one.

[Punnam]

Hi CTGuyton ,

Provide a sample workbook for fast action.


Punnam

[gak67]

Yes, it is possible. Could you upload a sample of what you're working with (with confidential information sanitized)? It makes it easier for us.

The best I can come up with without a sample file is that an offset/match combo sounds like the best option. Something like =OFFSET('Sheet 1'!$E1,MATCH(A2,'Sheet 1'!$D:$D,0)+1,) in Sheet 2 B2.

[CTGuyton]

test sheet.xls


"BJ's" is where I've copied and pasted the report I'm trying to get the times from. "Print This" is where I'd like for it to put the numbers. I took the first guy on the list and Highlighted in yellow the numbers I'm trying to get.

[gak67]

I had to change some of the spelling on your Print This tab to match the BJs tab, but this works:
test sheet.xls

[CTGuyton]

it says #Name? I'm in Excel 03, maybe mine's outdated or something?

[gak67]

It would pay to update your profile with that info so we know. The IFERROR function is only compatible with 2007 and after. That can be gotten around by copying the cells from your data (BJs) to you Print This tab for the employee name so they are identical for the match. I will upload a changed sheet shortly.

[gak67]

See attached. Copy the cell with "Molina Franklyn " in it from the BJ's tab (D413) and paste values into 'Print This'!B33 and you'll see that it then works. When using the MATCH function you need to make sure the text strings actually match, including leading and trailing spaces. I am assuming that the format of the data is the same each day, so once you have the Print This tab set up correctly you shouldn't need to change it except for new employees.
test sheet.xls

[CTGuyton]

Thanks a lot man!! This is awesome!!

[gak67]

Glad to help. If this has resolved your issue you should mark the thread as solved (at the top of the thread on the right click on Thread Tools).

If we have helped, consider adding reputation by clicking on the * at the bottom left of each post.

[CTGuyton]

I had to leave for work, but now that I'm here, I've looked it over and seems to work out great! I have one quick question. I added a 5th and 6th column to account for a second lunch or extra punch that might throw me off. When I did, it worked fine for everyone but one. Barak Garfield. All of them get 0s but his show up blank. What might cause that?

[gak67]

Maybe because he didn't have a lunch break. He only had 1 clock in and one clock out. Without seeing your sample file with how you calculated the extra columns I can't be certain.

[CTGuyton]

i just did the same formula but +3. I assumed the same thing you did so I tried deleting a row for another person but theirs showed up with 0s.

I've inputted tonight's report to see how it works and everything looks good. It's also corrected Barak's extra punches column too.


One thing I noticed. The report includes it's own page breaks which throws off the formula. I'm pretty sure I can fix that though by setting the report to show one employee per page.