display value from another cell selected on the basis of variables in a drop-down menu
I'm producing a spreadsheet to help me assess the financial viability of hiring employees for my business based on their qualifications and the number of days they work.
On a sheet called 'employees' I have separate tables for each kind of employee based on qualifications (e.g. 1st year apprentice, 2nd year apprentice, 3rd year apprentice, 4th year apprentice, Tradesperson). In each of these tables I have rows itemising expenses and columns for number of days per week worked (1, 2, 3, 4 and 5). The final row (29) gives me a total of the expenses. So cell D29 of each table gives me the expense for each employee on a 1 day per week basis, E29 gives me the expense on a 2 day per week basis, E30 3 days per week, E31 4 days, and E32 5 days.
Now on my first sheet called 'Variables' I want to pull the relevant data from the employee tables based on the variables I select. I have a table (Table 1) with a number of rows that can each represent an employee. The cells in column A contain a drop down menu allowing me to choose the kind of employee (e.g. 1st year apprentice, 2nd year apprentice...) Column B has a drop down menu that allows me to choose the number of days per week (e.g. 1, 2...) Now I want column C to display the relvant value from one of the employee tables.
If (in Table 1) I choose '1st year apprentice' in A4 and '5' (days per week) in B4 I want C4 to display the value from the cell E32 from the table '1st year apprentice'. And if I choose 'Tradesperson' in A5 and and '3' in B5 I want C5 to display the value from the cell E30 from the table 'Tradesperson'.
Is there a formula that can do this sort of thing or am I trying to get numbers to do something it isn't designed to do?
Thanks,
Ben.
iMac (27-inch Mid 2011), OS X Mavericks (10.9)