Trying to run a medication samples report w/ the same lot # but different check in dates
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.
Best Answer

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 Distributed@row 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 Distributed@row from the total Quantity, again.
Instead of just subtracting Distributed@row, 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!
Cheers,
Genevieve
Answers

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  Distributed@row
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 Distributed@row 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 Distributed@row from the total Quantity, again.
Instead of just subtracting Distributed@row, 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!
Cheers,
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!