Autofill

[fionalofthouse]

Hi,

Just wondering if the below is possible. I'm not the best at Excel, so please explain in dummy format haha!

This is for work. For context, I'm creating an overtime sheet which needs to be able to filter the amount of over time and travel time based off the job number.

On image attached, we type in the job number and ref, fill in the date of the overtime/travel time, and the persons name. We then fill in their hourly rate, their overtime rate, then the amount of overtime they've worked, and any travel time. The totals then get figured out by multiplying the OT worked by their OT rate, and travel time worked by their rate.

Basically, what I'm wanting is for when I type a persons name in, the 'Rate' box and 'OT rate' box pre-fill with that persons hourly rate / overtime rate (it's a nightmare trying to remember each persons rate). Is this possible?

Thank you

 

[Trixterz]

I am guessing that you have a separate sheet with all the employee name and rates. If so what is the structure of that sheet like? For example what column is the employee name, OT rate, and etc... I would recommend that you use a combination of index and vlookup formula to achieve your goal.

 

[fionalofthouse]

I am guessing that you have a separate sheet with all the employee name and rates. If so what is the structure of that sheet like? For example what column is the employee name, OT rate, and etc... I would recommend that you use a combination of index and vlookup formula to achieve your goal.

Thank you for your reply.

I have set up a data validation list with rates and OT rates, so I do have a sheet that just has each persons name and then their rate / OT rate. I'm not familiar with vlookup and index, could you help?

 

[Trixterz]

Here is a tutorial on Youtube.

 

[fionalofthouse]

AMAZING!! Thank you so much, got it working :D