What If Scenario and/or Pivot Table

[eightandup]

I need help and appreciate any provided. I need to run a ‘what-if’ and/or a
pivot table to find out the number of personnel needing to be hired based on
different criteria for 40 various jobs, over 11 years at four different
locations based on the three types of populations and/or population housing
types.

I am trying to be able to do the following:

enter the ‘Year’
and the ‘Job Titles’ be updated at each location and for each job title.

or

enter ‘Year’
enter ‘Location’
and the ‘Job Titles’ be updated at that location and for each job title.

Then I can consolidate the results.

This is where I am running into trouble and just can’t figure out how to get
it work and its probably going to end up being simple but, I just can’t see
it.

For each Year:
Population Type 1
Population Type 2
Population Type 3
Population Type 1 Hsging
Population Type 2 Hsging
Population Type 3 Hsging

Location1
location2
location3
location4

# Personnel Needed
Job Title A population >= 10,000

Job Title B <= 800 customers, 1 staff member per every 150 customers; if
customer population >= 800 then 1 staff member per every 200 customer
population

Job Title C (at .3 per 1500; formula will be =sum(population/1500)/1.7).

Would like to results something like this

Yr 2010 Location1 Location2 Location3
Job Title A: 1 0 1
Job Title B: 12 3 19
Job Title C: 8 5 13

Yr 2011 Location1 Location2 Location3
Job Title A: 1 0 1
Job Title B: 11 2 20
Job Title C: 8 0 16

and so on for each year, location and job title.

I have been trying different approaches and am feeling completely stupid. I
can get a pivot table but can't get the formulas figured out.

The 'what if' I’ve tried won’t let me reference a different worksheet and I
don’t want to put multiple columns of reference cells on an already large
worksheet.

Thank you