Jun 23 2022 06:18 AM
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.
@Hans Vogelaar i'll allow myself to tag you sir since you've been super-helpful last time :)
Thanks and have a nice day all
Jun 24 2022 06:01 AM
SolutionIf 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.
Jun 26 2022 11:37 PM
Jun 27 2022 03:00 AM
Let'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))
Jun 27 2022 05:47 AM
Jun 27 2022 06:35 AM
You could check if column F contains something other than "PAL", e.g. "OTHER" or a blank.
The formula could then be
=IF(OR(B13="",C13="",F13<>"PAL"),"",C13/IFERROR(VLOOKUP(A13,Sheet2!A:B,2,FALSE),60))
Jun 24 2022 06:01 AM
SolutionIf 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.