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

JJLewis

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.

Genevieve P.

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

JJLewis

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...

Genevieve P.

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