Forum Discussion
Solved
UOM dividing IF-THEN / other solution
Hi all
I've been designing simple delivery note generator, i've got products that are picked up from dropdown box as shown below, operator will only type first qty in trays and on side converter will show pallets qty which was simply dividing cell value by 60 (as that's how many trays majority of product will fit on pallet).
Now to my question - but what about if i.e. you select Caramel 4pk and this means that 90 will fit per pallet. Or other potential variants. What formula to put that upon selection from dropdown box it knows it's caramel and divides by 90, and for the rest by 60.
HansVogelaar i'll allow myself to tag you sir since you've been super-helpful last time 🙂
Thanks and have a nice day all
If caramel is the only exception, you could use the following formula in E13:
=IF(OR(B13="",C13=""),"",C13/IF(B13="CARAMEL 4PK",90,60))
This can be filled down.
If you have more exceptions, it would be better to create a lookup list with product in the first column and trays per pallet in the second column.
If caramel is the only exception, you could use the following formula in E13:
=IF(OR(B13="",C13=""),"",C13/IF(B13="CARAMEL 4PK",90,60))
This can be filled down.
If you have more exceptions, it would be better to create a lookup list with product in the first column and trays per pallet in the second column.
- hi,
thanks, work like a charm as always 🙂 but you're right - to be more proof for any changes in the future - creating a lookup list would be probably better longer term solution.
what would be the formula in E13 then?
kind regardsLet's say you create a list like this on another sheet Sheet2:
It contains only three products here, but it can be longer of course. The formula in E13 on the data sheet could then be
=IF(OR(B13="",C13=""),"",C13/XLOOKUP(A13,Sheet2!A:A,Sheet2!B:B,60))
if you have Microsoft 365 or Office 2021:
If you have an older version:
=IF(OR(B13="",C13=""),"",C13/IFERROR(VLOOKUP(A13,Sheet2!A:B,2,FALSE),60))
