Hi All,
I'm looking for a way to return different values based on the selection chosen in cells B2 & B3. Also cells B7 has two different choices.
Stages Sheet
B2 has in a drop down menu - VIC, TAS, WA, SA, ACT, QLD, NSW, NZ, ACT
B3 has in drop down menu - METRO, COUNTRY, REMOTE
B7 TO B12 has a selection of D (Day) or N (Night) making it 2 selections.
Rates Sheet
The data from the stage sheet selection is picked up from the rates sheet which will pick up the correct value depending on the 3 cells selected from the drop down menus
The formula is entered in D7 to D12, F7 to F12, H7 to H12 & so on where the value is returned. Cell C7 to C12, E7 to E12, G7 to G12 is the quantity which is a manual entry.
Hope this makes sense.
I'm looking for a way to return different values based on the selection chosen in cells B2 & B3. Also cells B7 has two different choices.
Stages Sheet
B2 has in a drop down menu - VIC, TAS, WA, SA, ACT, QLD, NSW, NZ, ACT
B3 has in drop down menu - METRO, COUNTRY, REMOTE
B7 TO B12 has a selection of D (Day) or N (Night) making it 2 selections.
Rates Sheet
The data from the stage sheet selection is picked up from the rates sheet which will pick up the correct value depending on the 3 cells selected from the drop down menus
The formula is entered in D7 to D12, F7 to F12, H7 to H12 & so on where the value is returned. Cell C7 to C12, E7 to E12, G7 to G12 is the quantity which is a manual entry.
Hope this makes sense.
| Excel 2010 | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ||||||||||
| 2 | Select------------------------------State | VIC | ||||||||
| 3 | Select----------------------------Region | METRO | ||||||||
| 4 | ||||||||||
| 5 | C 01 | C 02 | C 03 | |||||||
| 6 | Qty | $$ | Qty | $$ | Qty | $$ | ||||
| 7 | Category 1 | D | 11 | $11 | 4 | $4 | 7 | $7 | ||
| 8 | Category 2 | D | 4 | $8 | 4 | $8 | 4 | $8 | ||
| 9 | Category 3 | N | $0 | $0 | $0 | |||||
| 10 | Category 4 | N | $0 | $0 | $0 | |||||
| 11 | Category 5 | N | $0 | $0 | $0 | |||||
| 12 | Category 6 | N | $0 | $0 | $0 | |||||
| 13 | Total | 15 | $19 | 8 | $12 | 11 | $15 | |||
Stages | ||||||||||
| Excel 2010 | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
| 1 | VIC | TAS | ||||||||||||||||||
| 2 | METRO | COUNTRY | REMOTE | METRO | COUNTRY | REMOTE | ||||||||||||||
| 3 | DAY | NIGHT | DAY | NIGHT | DAY | NIGHT | DAY | NIGHT | DAY | NIGHT | DAY | NIGHT | ||||||||
| 4 | Category 1 | $1.00 | $10.00 | $21.00 | $31.00 | $41.00 | $51.00 | $101.00 | $201.00 | $21.00 | $31.00 | $41.00 | $51.00 | |||||||
| 5 | Category 2 | $2.00 | $11.00 | $22.00 | $32.00 | $42.00 | $52.00 | $102.00 | $202.00 | $22.00 | $32.00 | $42.00 | $52.00 | |||||||
| 6 | Category 3 | $3.00 | $12.00 | $23.00 | $33.00 | $43.00 | $53.00 | $103.00 | $203.00 | $23.00 | $33.00 | $43.00 | $53.00 | |||||||
| 7 | Category 4 | $4.00 | $13.00 | $24.00 | $34.00 | $44.00 | $54.00 | $104.00 | $204.00 | $24.00 | $34.00 | $44.00 | $54.00 | |||||||
| 8 | Category 5 | $5.00 | $14.00 | $25.00 | $35.00 | $45.00 | $55.00 | $105.00 | $205.00 | $25.00 | $35.00 | $45.00 | $55.00 | |||||||
| 9 | Category 6 | $6.00 | $15.00 | $26.00 | $36.00 | $46.00 | $56.00 | $106.00 | $206.00 | $26.00 | $36.00 | $46.00 | $56.00 | |||||||
Rates | ||||||||||||||||||||
