Help needed to gather data from multiple sheets to calculate

bleinfuss

Community,

I am really hoping you can assist me with this. I am creating a process to reconcile quantities of medications at a location. So, here are the details:

I have 3 sheets, AM Count, Controlled Substance Dispensed and Controlled Substance Restock. All 3 sheets' data is submitted via a form by an end user.

The AM Count is a 1 time a day submission as is the Controlled Substance Restock. My sheets look like this:

The Controlled Substance Dispensed sheet will have multiple entries a day. Sheet looks like this:

I am able to get the total for each date in the Versed Dispensed Total column with this formula:

=SUMIFS([Versed Dispensed (Total vials used)]:[Versed Dispensed (Total vials used)], Date:Date, Date@row)

So this brings me to where I am stuck. I need to take the AM Count for each day, subtract the Versed Dispensed Total for each day, add the Versed Restock Count to calculate an end of day total.

My thought was to have a separate calculation sheet but I am unsure of how to pull into a calculation sheet a single value for the "Versed Dispensed Total" per day.

The other part I am struggling with is on the calculation sheet (once I am able to pull in the "Versed Dispensed Total" for each day) I am not sure how to pull the data in on the same line based on the date so I can put the calculation into the "Versed EDO Count" column:

Lastly, can Smartsheet auto-populate the Versed EOD Count with the formula I need or will I have to drag the formula down before the data get populated?

I know I asked like 8 questions on this post but I truly appreciate any help.

Thank you in advance

Mark Safran

Hi @bleinfuss

Based on what you've outlined above, it seems like you're 90% of the way there. On your calculations sheet you'll need a single row for each date that you want to evaluate. To avoid this becoming a never-ending list, you may want to consider using the TODAY( ) function, and dictate a finite quantity of dates that you want to run calcs for. This isn't required, and you could certainly pre-populate your rows with consecutive dates well into the future, but at some point this will require maintenance. But putting that aside...

For your AM Counts Total, Dispensed Total, and Restock Count columns, you're basically going to use the same SUMIFS formula in each of them, but just update the references to the respective sheets where the data lives. For example, the formula for the AM Counts Total would be =SUMIFS({AM_Counts_Sheet Qty Column Reference}, {AM_Counts_Sheet Date Column Reference}, Date@row) . This will get you the total qty of the morning counts for the given date. Follow this same logic for the next two columns to get the total qty of dispensed and the total qty of restocked. Then your EOD Count column can be a formula that is =[AM Count Total] - [Dispensed Total] + [Restock Total]. To make that "auto-populate" down the entire column, you can right click on the cell once the formula is written and choose "convert to column formula".

-MCS