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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!