Pop up box:create an overtime calculator

I am trying to create an overtime calculator for my colleagues and myself to
use in work.

Now, our payscales are different dependant on the amount of time we have
worked for the company. So, they are similar to below - pay per hour

1 year = £9.49
2 years = £10.56
3 years = £12.72

I will be creating a column listing these values.

In the overtime calculator, I have Cell A which lists the amount of hours
worked, formatted in time, ie 5:30. Now, what I would like is for some sort
of drop down menu on the spreadsheet. The user can select from this menu the
amount of years they have worked for the company. Then in cell 2, I would
like the spreadsheet to automatically multiply the amount of hours worked in
column A by the pay that matches the selected amount of time?

Does that make sense? Any ideas?

Neil,

The way that I would do it would be in an unused area od the spreadsheet -
say columns L & M list the number of years in ascending order in L and the
corresponding pay in M then with the overtime hours in A1, enter the number
of years in B1, (it can be greater than the highest number in Column L), and
in C1 enter the formula:

=A1*24*LOOKUP(B1,L1:L3,M1:M3)

If you wish columns L & M can be hidden and the formula will still work.
--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

"Neil Greenough" <[email protected]> wrote in message
news:%[email protected]...
>I am trying to create an overtime calculator for my colleagues and myself
>to use in work.
>
> Now, our payscales are different dependant on the amount of time we have
> worked for the company. So, they are similar to below - pay per hour
>
> 1 year = £9.49
> 2 years = £10.56
> 3 years = £12.72
>
> I will be creating a column listing these values.
>
> In the overtime calculator, I have Cell A which lists the amount of hours
> worked, formatted in time, ie 5:30. Now, what I would like is for some
> sort of drop down menu on the spreadsheet. The user can select from this
> menu the amount of years they have worked for the company. Then in cell 2,
> I would like the spreadsheet to automatically multiply the amount of hours
> worked in column A by the pay that matches the selected amount of time?
>
> Does that make sense? Any ideas?
>

You might like to add some restrictions to the data entered in B1 by
selecting it and then Data | Validation pointing to L1:L3 (to use
Sandy's example). This way you can't enter "one" instead of 1.

Pete