I am trying to build form with the same drop down boxes that has ten rows and five columns. I can get the first row to work, but when I copy down it doesn't copy properly.
| Waste.V1.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | Item | Size | Weight | Length | ||||||||||
| 2 | Picket | 5/8" x .045" | 0.0100 | 48 | Preparation table | |||||||||
| 3 | Picket | 5/8" x .045" | 0.0100 | 54 | Item | Size | Length | Weight | ||||||
| 4 | Picket | 5/8" x .045" | 0.0100 | 60 | Picket | 5/8" x .045" | 48 | 0.01 | ||||||
| 5 | Picket | 5/8" x .045" | 0.0100 | 70 | Post | 3/4" x .055" | 54 | 0.0151 | ||||||
| 6 | Picket | 5/8" x .045" | 0.0100 | 72 | Rail | 1" x .060" | 60 | 0.0226 | ||||||
| 7 | Picket | 5/8" x .045" | 0.0100 | 84 | 70 | |||||||||
| 8 | Picket | 5/8" x .045" | 0.0100 | 96 | 72 | |||||||||
| 9 | Picket | 5/8" x .045" | 0.0100 | 120 | 84 | |||||||||
| 10 | Picket | 5/8" x .045" | 0.0100 | 144 | 96 | |||||||||
| 11 | Picket | 5/8" x .045" | 0.0100 | 156 | 120 | |||||||||
| 12 | Picket | 5/8" x .045" | 0.0100 | 288 | 144 | |||||||||
| 13 | Picket | 3/4" x .055" | 0.0151 | 48 | 156 | |||||||||
| 14 | Picket | 3/4" x .055" | 0.0151 | 54 | 288 | |||||||||
| 15 | Picket | 3/4" x .055" | 0.0151 | 60 | ||||||||||
Multiple dropdown | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I4:I6 | I4 | =UNIQUE(A2:A166) |
| J4:J6 | J4 | =UNIQUE(FILTER(B2:B166, A2:A166=Sheet1!A2)) |
| K4:K14 | K4 | =UNIQUE(FILTER(D2:D166, A2:A166=Sheet1!A2)) |
| L4:L6 | L4 | =UNIQUE(FILTER(C2:C166, A2:A166=Sheet1!A2)) |
| Dynamic array formulas. | ||
| Waste.V1.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Item | Size | Length | Weight | QTY | Total Weight | |||
| 2 | Picket | 3/4" x .055" | 96 | 0.0151 | 1.0000 | 1.4496 | |||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2 | D2 | =VLOOKUP(B2,'Multiple dropdown'!B2:D166,2,FALSE) |
| F2 | F2 | =(C2*D2)*E2 |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| A2 | List | ='Multiple dropdown'!$I$4:$I$6 |
| B2 | List | ='Multiple dropdown'!$J$4:$J$13 |
| C2 | List | ='Multiple dropdown'!$K$4:$K$14 |
