Excel experts in here please :)

[MrKeeno]

Hi guys

Im looking to make a spreadsheet that will help me work out exactly what I should be paid at the end of each month.

Up until last month I was on steady hours and my pay was exactly the same each time. However I have now moved to a spare man duty that means I can cover any duty and any hours throughout the month. I really want to keep a close eye on this as it was a major gripe with the last guy that did it, he was constantly trying to get hours owed to him.

Stick with me on this guys as it may involve a bit of back and fourth so your help will be appreciated.

1st thing I would like to do is create an 'If' function that will allow me to input a duty number into a cell, which will then output the amount of overtime hours relevant to that duty in another cell. Something like this, although I know its not right :

=IF(C3=152,D3=2.5) - 152 is the duty number and it has 2.5 hrs overtime attached to it.

Cheers

 

[wonko]

Hi busman,
If there is a long list of duty numbers then using if statements would be very messy.
What I'd suggest you look at is using the vlookup function.

If you put the duty number and overtime hours in columns A and B of a worksheet, e.g.

152 2.5
153 3.5
154 4.5

Then in another worksheet, if the cell you type the duty number into is A1, then put the following formula into B1: "=VLOOKUP(A1, OtherSheetName!$A$1:$B$999,2,FALSE)" it shoud retrieve the corresponding overtime hours.

Hope this gets you started

 

[MarcLister]

As wonko says a VLOOKUP function would do what you need there. You could even use a drop-down list to make sure you don't type a wrong number.