I am creating a resource allocation spreadsheet. On one sheet I am list various Job Titles and I ask the user to input the Salaries associated with them, and the Number of each job title at the company (ie: Secretary: makes $45,000 and there are 3 of them, etc.). These are all in different cells, nothing tricky here.
On a second sheet I have a list of tasks, and am asking the user to select from a drop-down list of the job titles to indicate which job personnel work on each task, and an amount of time they spend on each task. There iwll be a drop down list for the job description selection, and then a field for entry of the time spent each week on each task. I have created the validated list for the drop-down, no problem.
My confusion is that I want the system to know that when the user selects "Secretary" and 8 hours, the system will use $45,000 per year and 8 hours per week in the calculation of how much the time allocation costs.
So, when I want to use an output calculation field somewhere that calculates and shows the total cost of the time spent doing a task, all the user sees is a dropdown list and selects "Secretary" and a field where he enters "8 hours per week", and the caculation knows to use $45,000 per year and 8 hours per week in the calculation. In other words, I don't want the user to have to input the $45,000 on this sheet, just the job title.
basically, I want to assign values (salaries, already known for each job title) to variables (the job title selected in the field) and use the values behind the scenes...
All i can find on this is combining text and values in a cell, which is not what I want to do. It is also enough variables that I don't want to use a lot of "if" statements.
thanks for the help!!!
On a second sheet I have a list of tasks, and am asking the user to select from a drop-down list of the job titles to indicate which job personnel work on each task, and an amount of time they spend on each task. There iwll be a drop down list for the job description selection, and then a field for entry of the time spent each week on each task. I have created the validated list for the drop-down, no problem.
My confusion is that I want the system to know that when the user selects "Secretary" and 8 hours, the system will use $45,000 per year and 8 hours per week in the calculation of how much the time allocation costs.
So, when I want to use an output calculation field somewhere that calculates and shows the total cost of the time spent doing a task, all the user sees is a dropdown list and selects "Secretary" and a field where he enters "8 hours per week", and the caculation knows to use $45,000 per year and 8 hours per week in the calculation. In other words, I don't want the user to have to input the $45,000 on this sheet, just the job title.
basically, I want to assign values (salaries, already known for each job title) to variables (the job title selected in the field) and use the values behind the scenes...
All i can find on this is combining text and values in a cell, which is not what I want to do. It is also enough variables that I don't want to use a lot of "if" statements.
thanks for the help!!!
