Hallo,
I will try my best to explain what I want, it's probably super easy but can't find an answer on google..
(don't mind all the formulas, there is probably a easier way but it works (?)
)
Here what I'm trying to do: Cell B14 & B15 change value (depends on parameters wich one changes (they never change together, its always just one)), what I want is a formula like "=B14" but than with both I tried "=B14:B15" but the issue is it does this than (printscreen):
What I want is that he only takes the one thats not 0 but keeps it in the same cell, now he's doing it as followed
I will try my best to explain what I want, it's probably super easy but can't find an answer on google..
(don't mind all the formulas, there is probably a easier way but it works (?)
)Here what I'm trying to do: Cell B14 & B15 change value (depends on parameters wich one changes (they never change together, its always just one)), what I want is a formula like "=B14" but than with both I tried "=B14:B15" but the issue is it does this than (printscreen):
What I want is that he only takes the one thats not 0 but keeps it in the same cell, now he's doing it as followed
| TCA berekening.xlsm | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Benodigde parameters | |||||
| 2 | ||||||
| 3 | Parameters : ACS-002 Ind Lait | |||||
| 4 | Klein bedrijf? | Nee | ||||
| 5 | Aantal Producttypes: | 4 of meer | ||||
| 6 | Producttypes: | 1 | ||||
| 7 | Productstromen naar de dierenvoeding?: | |||||
| 8 | ||||||
| 9 | FALSE | |||||
| 10 | 4 of meer | |||||
| 11 | Klein bedrijf | FALSE | ||||
| 12 | Normaal bedrijf | 16 | ||||
| 13 | Dierenvoeders | 0 | ||||
| 14 | Totaal klein | 0 | ||||
| 15 | Totaal normaal | 16 | ||||
| 16 | Totaal auditduur: | 0 | ||||
| 17 | 16 | |||||
| 18 | ||||||
| 19 | ||||||
| 20 | ||||||
| 21 | ||||||
| 22 | Dropdown list: Klein bedrijf | Dropdown list: Aantal | Dropdown list: Producttypes | Dropdown list: Dierenvoeding | ||
| 23 | Ja | 1 | UHT-consumptiemelkdranken en UHT- room | Ja | ||
| 24 | Nee | FALSE | Gesteriliseerde consumptiemelkdranken en room | Nee | ||
| 25 | 2 | Gepasteuriseerde consumptiemelkdranken en room | ||||
| 26 | FALSE | Gefermenteerde melk | ||||
| 27 | 3 | Thermisch behandelde gefermenteerde melk | ||||
| 28 | FALSE | Verse kaas | ||||
| 29 | 4 of meer | Harde kaas op basis van gepasteuriseerde melk | ||||
| 30 | TRUE | Mozzarella op basis van gepasteuriseerde melk | ||||
| 31 | Zachte kaas op basis van gepasteuriseerde melk | |||||
| 32 | Zure boter op basis van gepasteuriseerde melk, verse zure karnemelk, thermisch behandelde zure karnemelk | |||||
| 33 | Melkpoeder | |||||
| 34 | Neutrale desserts | |||||
| 35 | Rauwmelkse kazen | |||||
| 36 | Zure boter op basis van rauwe melk en verse zure karnemelk | |||||
| 37 | ||||||
| 38 | ||||||
| 39 | ||||||
| 40 | Normaal | small enterprise | ||||
| 41 | Aantal producttypes | Te besteden mandagen (uren) | Aantal producttypes | Te besteden mandagen (uren) | ||
| 42 | 1 | 10 | 1 | 4 | ||
| 43 | 2 | 12 | 2 | 6 | ||
| 44 | 3 | 14 | 3 | 8 | ||
| 45 | 4 of meer | 16 | 4 of meer | 10 | ||
| 46 | Dierenvoeders (uren) | |||||
| 47 | 2 | |||||
ACS-002 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B9 | B9 | =IF($B$4="Ja","TRUE") |
| B10 | B10 | =$B$5 |
| B11 | B11 | =IF(B4="Ja",IF(B24,D42,IF(B26,D43,IF(B28,D44,IF(B30,D45,"ERROR"))))) |
| B12 | B12 | =IF(B4="Nee",IF(B24,B42,IF(B26,B43,IF(B28,B44,IF(B30,B45,"ERROR"))))) |
| B13 | B13 | =IF(B7="Ja",A47,) |
| B14 | B14 | =IF(B11<>"FALSE",B11+B13,) |
| B15 | B15 | =IF(B12<>"FALSE",B12+B13,) |
| B16:B17 | B16 | =B14:B15 |
| B24 | B24 | =B10=B23 |
| B26 | B26 | =B10=B25 |
| B28 | B28 | =B10=B27 |
| B30 | B30 | =B10=B29 |
| Dynamic array formulas. | ||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B4 | List | =$A$23:$A$24 |
| B5 | List | =$B$23:$B$29 |
| B6 | List | =$C$23:$C$36 |
| B7 | List | =$D$23:$D$24 |
