BrutalLogiC
Active Member
- Joined
- Feb 26, 2006
- Messages
- 267
- Office Version
- 2016
- Platform
- Windows
hi I am trying to work out a formula for the cells in yellow to return the Rate from the schedule of rates based on the selection of (1) department (2) resource and (3) rate type.
e.g.
if I select Dept1, Resource 1 and Monthly, the rate should be 10,000 in cell I5
if I select Dept2, Resource 5 and Daily, the rate should be 98 in cell I9
I haven't posted the full 'schedule of rates' range as it's a bit large but there are 10 departments, 10 resources and 3 rate types. The rates for each resource are the same regardless of the department but they might change so that's why I have the department column. Hope I am not over complicating it.... if I only had 1 rate type I think easier but I can't work out how to cope with 3
e.g.
if I select Dept1, Resource 1 and Monthly, the rate should be 10,000 in cell I5
if I select Dept2, Resource 5 and Daily, the rate should be 98 in cell I9
I haven't posted the full 'schedule of rates' range as it's a bit large but there are 10 departments, 10 resources and 3 rate types. The rates for each resource are the same regardless of the department but they might change so that's why I have the department column. Hope I am not over complicating it.... if I only had 1 rate type I think easier but I can't work out how to cope with 3
Service Pricing Calculator.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
B | C | F | G | I | ||||||
4 | No | Department | Resource | Rate Type | Rate | |||||
5 | 1 | Dept1 | Resource 1 | Monthly | ||||||
6 | Resource 3 | Daily | ||||||||
7 | Resource 7 | Hourly | ||||||||
8 | 2 | Dept2 | Resource 1 | Monthly | ||||||
9 | Resource 5 | Daily | ||||||||
10 | ||||||||||
11 | 3 | Dept3 | Resource 1 | Monthly | ||||||
12 | Resource 4 | Daily | ||||||||
13 | Resource 2 | Hourly | ||||||||
14 | 4 | Dept4 | Resource 7 | Monthly | ||||||
15 | ||||||||||
16 | ||||||||||
Pricing |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
C5:C31 | List | =Resources!$I$7:$I$11 |
F5:F31 | List | =Resources!$K$7:$K$16 |
G5:G31 | List | =Resources!$M$7:$M$9 |
Service Pricing Calculator.xlsx | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
4 | Schedule of Rates | ||||||
5 | Rate Type | ||||||
6 | Department | Resource | Monthly | Daily | Hourly | ||
7 | Dept1 | Resource 1 | 10,000 | 435 | 54 | ||
8 | Dept1 | Resource 2 | 12,000 | 522 | 65 | ||
9 | Dept1 | Resource 3 | 14,000 | 609 | 76 | ||
10 | Dept1 | Resource 4 | 16,000 | 696 | 87 | ||
11 | Dept1 | Resource 5 | 18,000 | 783 | 98 | ||
12 | Dept1 | Resource 6 | 5,000 | 217 | 27 | ||
13 | Dept1 | Resource 7 | 6,000 | 261 | 33 | ||
14 | Dept1 | Resource 8 | 7,000 | 304 | 38 | ||
15 | Dept1 | Resource 9 | 8,000 | 348 | 43 | ||
16 | Dept1 | Resource 10 | 9,000 | 391 | 49 | ||
17 | Dept2 | Resource 1 | 10,000 | 435 | 54 | ||
18 | Dept2 | Resource 2 | 12,000 | 522 | 65 | ||
19 | Dept2 | Resource 3 | 14,000 | 609 | 76 | ||
20 | Dept2 | Resource 4 | 16,000 | 696 | 87 | ||
21 | Dept2 | Resource 5 | 18,000 | 783 | 98 | ||
22 | Dept2 | Resource 6 | 5,000 | 217 | 27 | ||
23 | Dept2 | Resource 7 | 6,000 | 261 | 33 | ||
24 | Dept2 | Resource 8 | 7,000 | 304 | 38 | ||
25 | Dept2 | Resource 9 | 8,000 | 348 | 43 | ||
26 | Dept2 | Resource 10 | 9,000 | 391 | 49 | ||
27 | Dept3 | Resource 1 | 10,000 | 435 | 54 | ||
28 | Dept3 | Resource 2 | 12,000 | 522 | 65 | ||
29 | Dept3 | Resource 3 | 14,000 | 609 | 76 | ||
Resources |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E7:E29 | E7 | =D7/$D$3 |
F7:F29 | F7 | =E7/$E$3 |