Trying to run a medication samples report w/ the same lot # but different check in dates

Answered - Pending Review

LOT # Anoro Ellipta 62.5 mcg 1122334455 10/06/21 10 10/13/21 zzztest 25 -15 false

LOT # Anoro Ellipta 62.5 mcg 1122334455 10/08/21 10 10/13/21 zzztest 25 -15 false

zzztest 01/01/00 Anoro Ellipta 62.5 mcg 1122334455 25 10/07/21 zzztest true true zzztest

I am trying to get an accurate count on samples but, if I change the received date the total adjusts for each individual check in date instead of the lot #?! How do I adjust to just the lot #?

This is a copy of the report ran...

Thank You in advanced.


  • JJLewisJJLewis
    edited 10/09/21

    This is the receiving sheet...

    Formulas used in the Distributed and On Hand columns are...

    =SUM(COLLECT({Distribution QTY}, {Distribution - Lot #}, [Lot #]@row))

    =[Received Amount]@row - [email protected]

    This is the distribution sheet...

    This is the report...

    Hope this helps...

  • Hi @JJLewis

    If I'm understanding you correctly, you currently have two formulas which identify for each row the Total Received amount and the Total Distributed amount in order to give you an overall picture of what's On Hand.

    However this number is input as a total but on each individual line.

    This means that when you subtract [email protected] from the Distribution Quantity, while the first row give you the correct answer, the second row does not take into account the first row's On Hand amount, and still only subtracts [email protected] from the total Quantity, again.

    Instead of just subtracting [email protected], we'll want to SUM the Distributed column by the LOT Number using a SUMIF formula. It might still only return the current row if there's just one row for this Lot number, but when you have multiple rows with the same Lot number it will add them together before subtracting it from the Quantity.

    Try this in your On Hand column:

    =[Received Amount]@row - SUMIF([Lot #]:[Lot #], [Lot #]@row, Distributed:Distributed)

    Then in your Report you won't want to SUM the On Hand column, as each cell will give you the total eOn Hand. Does this make sense?

    Let me know if I've understood you question and if this works for you!



Sign In or Register to comment.