Hi All,
I recently asked for help for a dropdown list that is dependent on two (2) other dropdowns, i.e., you after selecting the first dropdown, the second dropdown is only choices related to the first, and then the third dropdown narrows the choice down to 1 or 2 selections.
Previous thread is here: 3 dependent drop downs
I followed Akuini's suggestion and downloaded his file, setup all the tables etc. as in the file, and the first two (2) dropdowns work just fine, the last one is the issue.
When I have the formula (just formula, no dropdown) the value changes depending on drop1 and 2, which is great. When I enter the formula into the data validation box to create a dropdown it stops working. The caveat to this is also, that the formula is on a different sheet, if I put the same formula on the same sheet as drop 1 & 2 it gives the #N/A error, although the are correct
Any help fixing this problem is hugely appreciated. Table below is truncated as there were more than 3k cells and it didn't copy, but it still doesn't work w/this truncated version.
I recently asked for help for a dropdown list that is dependent on two (2) other dropdowns, i.e., you after selecting the first dropdown, the second dropdown is only choices related to the first, and then the third dropdown narrows the choice down to 1 or 2 selections.
Previous thread is here: 3 dependent drop downs
I followed Akuini's suggestion and downloaded his file, setup all the tables etc. as in the file, and the first two (2) dropdowns work just fine, the last one is the issue.
When I have the formula (just formula, no dropdown) the value changes depending on drop1 and 2, which is great. When I enter the formula into the data validation box to create a dropdown it stops working. The caveat to this is also, that the formula is on a different sheet, if I put the same formula on the same sheet as drop 1 & 2 it gives the #N/A error, although the are correct
Any help fixing this problem is hugely appreciated. Table below is truncated as there were more than 3k cells and it didn't copy, but it still doesn't work w/this truncated version.
| new cost.xlsx | ||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | |||
| 1 | ||||||||||||||||||||||||||||||||||||
| 2 | HELPER TABLE, starting at AA4 | |||||||||||||||||||||||||||||||||||
| 3 | Notes | Mfg. Process | Equipment | Batch Size | Equipment Notes | Batch Size | # of batches | MFG Hours / Batch | Form/MFG Modifier | No. of technicians | Total Hours | Mfg Process | Equipment | BatchSize | ||||||||||||||||||||||
| 4 | 1 | Melt Spray Congeal | Optimµm® Melt Spray 2 L | 400 g - 30 kg | 1 | Melt Spray Congeal | Optimµm® Melt Spray 2 L | 100g-1.6 kg | 1 | Fluid Bed | Melt Spray Congeal | Microencapsulation | 0 | |||||||||||||||||||||||
| 5 | 2 | 1 | Melt Spray Congeal | Optimµm® Melt Spray 30 L | 4-25 Kg | 2 | Glatt GPCG-3 | Optimµm® Melt Spray 2 L | 1 Gallon Reactor | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||||||||
| 6 | 3 | 1 | Microencapsulation | 5 Gallon Reactor | 350g-1,500g | Glatt GPCG-5 | Optimµm® Melt Spray 30 L | 1 liter Reactor | ||||||||||||||||||||||||||||
| 7 | 4 | 1 | Microencapsulation | 1 Gallon Reactor | 100g-400g | Glatt GPCG-60/120 | 1000 Gallon Reactor | |||||||||||||||||||||||||||||
| 8 | 5 | 1 | Microencapsulation | 1 liter Reactor | 20-100g | Glatt Mini | 200 Gallon Reactor | |||||||||||||||||||||||||||||
| 9 | 6 | 1 | Microencapsulation | 1000 Gallon Reactor | 250-1000kg | Vector VFC-1 | 5 Gallon Reactor | |||||||||||||||||||||||||||||
| 10 | 7 | 1 | Microencapsulation | 1000 Gallon Reactor | 125-500kg | Vector VFC-3 | 500 Gallon Reactor | |||||||||||||||||||||||||||||
| 11 | 8 | 1 | Microencapsulation | 500 Gallon Reactor | 125-500kg | |||||||||||||||||||||||||||||||
| 12 | 9 | 1 | Microencapsulation | 200 Gallon Reactor | 40-200kg | |||||||||||||||||||||||||||||||
| 13 | 10 | 1 | Fluid Bed | Glatt GPCG-3 | 550-2000g | |||||||||||||||||||||||||||||||
| 14 | Mfg Total | 0 | Fluid Bed | Glatt GPCG-3 | 550-2400g | 3 | | | Fluid Bed|Glatt GPCG-3 | Fluid Bed|Glatt GPCG-5 | Fluid Bed|Glatt GPCG-60/120 | Fluid Bed|Glatt Mini | Fluid Bed|Vector VFC-1 | Fluid Bed|Vector VFC-3 | Melt Spray Congeal|Optimµm® Melt Spray 2 L | Melt Spray Congeal|Optimµm® Melt Spray 30 L | Microencapsulation|1 Gallon Reactor | Microencapsulation|1 liter Reactor | Microencapsulation|1000 Gallon Reactor | Microencapsulation|200 Gallon Reactor | Microencapsulation|5 Gallon Reactor | Microencapsulation|500 Gallon Reactor | |||||||||||||||
| 15 | Report | Fluid Bed | Glatt GPCG-5 | 2-18 kg | 4 | Fluid Bed | Fluid Bed | Fluid Bed | Fluid Bed | Fluid Bed | Fluid Bed | Melt Spray Congeal | Melt Spray Congeal | Microencapsulation | Microencapsulation | Microencapsulation | Microencapsulation | Microencapsulation | Microencapsulation | |||||||||||||||||
| 16 | Total for Milestone | 0 | Fluid Bed | Glatt Mini | 50-250g | Glatt GPCG-3 | Glatt GPCG-5 | Glatt GPCG-60/120 | Glatt Mini | Vector VFC-1 | Vector VFC-3 | Optimµm® Melt Spray 2 L | Optimµm® Melt Spray 30 L | 1 Gallon Reactor | 1 liter Reactor | 1000 Gallon Reactor | 200 Gallon Reactor | 5 Gallon Reactor | 500 Gallon Reactor | |||||||||||||||||
| 17 | same sheet | #N/A | Total | Fluid Bed | Vector VFC-1 | 300g-1.8kg | 5 | 550-2000g | 2-10kg | 40-90kg | 50-250g | 300g-1.8kg | 700-2400g | 100g-1.6 kg | 4-25 Kg | 100g-400g | 20-100g | 125-500kg | 40-200kg | 350g-1,500g | 125-500kg | |||||||||||||||
| 18 | Fluid Bed | Vector VFC-3 | 700-2400g | 550-2400g | 2-18 kg | 90-300kg | 250-1000kg | |||||||||||||||||||||||||||||
| 19 | Fluid Bed | Glatt GPCG-5 | 2-10kg | |||||||||||||||||||||||||||||||||
| 20 | Fluid Bed | Glatt GPCG-60/120 | 40-90kg | |||||||||||||||||||||||||||||||||
Sheet2 | ||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| T4:W4 | T4 | =TRANSPOSE(SORT(UNIQUE(Process6[Mfg Process]))) |
| T5:T10,W5:AF5,V5:V10,U5:U6 | T5 | =SORT(UNIQUE(FILTER(Process6[Equipment],Process6[Mfg Process]=T4,""))) |
| L4:L13 | L4 | =IF($G4=0,"",PRODUCT(H4:K4)) |
| L14 | L14 | =SUM(L4:L13) |
| T14:AH14 | T14 | =TRANSPOSE(SORT(UNIQUE(CONCATENATE(Process6[Mfg Process]&"|"&Process6[Equipment]),FALSE,FALSE))) |
| T15,U15:AH16 | T15 | =IFERROR(TEXTSPLIT(T14,,"|",1),"") |
| L16 | L16 | =SUM(L14:L15) |
| E17 | E17 | =XLOOKUP($C4&"|"&$D4,$K$15:$BA$15,$K$18:$BA$18)# |
| T17,X17:AD17,AF17:AH17,AE17:AE18,U17:W18 | T17 | =SORT(UNIQUE(FILTER(Process[BatchSize],(Process[Mfg Process]=T15)*(Process[Equipment]=T16),""))) |
| Dynamic array formulas. | ||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| H4:H13 | List | =No_of_batches |
| K4:K13 | List | =Lists!$D$16:$D$20 |
| E18 | List | =XLOOKUP(C4&"|"&D4,$K$15:$BA$15,$K$18:$BA$18)# |
| C4 | List | =$T$4# |
| D4 | List | =XLOOKUP($C4,$T$4:$AF$4,$T$5:$AF$5)# |
| E4 | List | =XLOOKUP($C4&"|"&$D4,$K$15:$BA$15,$K$18:$BA$18)# |
