An Excel question
The project in hand is a Excel workbook which will handle the allocation of 'jobs completed per month bonus kitty' and the allocation of such bonuses to qualifying staff members in any given month.
'Worksheet1' primarily calculates the bonus kitty amount earned for a specific number of completed jobs in any month.
There are other calculations there (minimum bonus guarantees, total earnings, projected annual earnings etc) but these do not affect the core question.
The core data lines are:
1) Job number (1 - 40)
2) Amount allocated to 'the kitty' per job (jobs 1 - 6 earn £22 each, jobs 7 - 15 earn £25 each, jobs 16 - 25 earn £30 each etc etc)
and from this a simple calculation of:
3) Total amount allocated to allocated to 'the kitty' in a given month based on qualifying jobs completed
'Monthly worksheets' count the qualifying jobs completed - and will subsequently share out the bonus kitty.
Again there are some other calculations subsequently (monthly staff costs, actual revenue, operating profit/loss etc) but again these do not affect the core question.
What I want to do is use the 'value' in the box showing qualifying jobs in the month (a number between 1 and 40) to 'lookup' the corresponding 'kitty value' in 'worksheet 1' and insert it in another cell in the monthly worksheet.
i.e
qualifying jobs counted = 16 (calculated on 'monthly worksheet')
16 jobs completed = £426 bonus (already calculated and displayed on 'worksheet 1
insert £426 in appropriate cell on 'monthly worksheet'
As I understand it, 'if functions' can't cope with this because of the number of options (1 - 40, in practice I could probably do away with 1 - 5 but this doesn't make a difference) but will be delighted if someone tells me otherwise.
I have looked at 'lookup' (in its various versions: VLOOKUP etc) and 'pivot tables' (neither of which are my areas of expertise admittedly) but can't see a way to do this.
If someone could just point me in the right direction I have a coffee table full of manuals to work through the detail - thanks in advance