Multiple Questions about Index Match and referencing other sheets

Confusedbuttrying

Hey heroes!

Okay, long story short - I work in EMS and am trying to create a new way for us to log our narcotics using Smartsheets. Its a pretty big system and I have everything planned out on how I am going to do it and have created all of the "leg work" of the sheets that will help the counts and automations going. With that being said, I am having a hard time of connecting them. So, I am seeking help/assistance with the following:

1. I have created a master sheet called "Narc Base" that should have a running total of medicine in each rig. These numbers will change based off the crews using their "medication form" usage smartsheet and restock. Issue is, I am not sure how to link the medication to the specific unit using it. Pictures attached. Basically, I want to match the unit and total on hand on the source sheet to my Narc Base form. Column names that I need are "Unit Number" to match the unit number to the "Unit Number" on Narc base form, then I need to match "Amount of Fentanyl on Hand" to "Crew submitted Fentanyl" and "# Fentanyl Left" to "Running Total Fentanyl" on the Narc Base sheet.

I think once I am able to figure this one out, the rest should fall into place.

Thanks in advance.

Jgorsich

It is hard to suggest specific formulas based off of your screenshots, so I'll make a couple of observations that might be helpful instead.

first - I presume for their "usage form" you are using an actual form, correct? Vs just having them type into a grid, I mean. That form needs to capture their unit number as well and add it to the same grid as where you are capturing usage.

Second - Index(match()) is used when you are trying to index through some list of data and you use the match() part to figure out how far to index into it. What you want is something more like a sumifs() formula - where you add up all the data in a given column based on criteria in other columns. So, to track inventory, you could use a sumifs() to pull the starting inventory for any given medicine for any given unit number, add to that the result of a second sumifs() that looks at restocks that are of that medicine and to that given unit number, subtract from that the result of a third sumifs() that looks at usages of that medicine by that given unit number.

Because you have each medicine in a different column (vs a column for "Medicine Name" and another for "Quantity" and just having them all together) you are going to need unique formulas for each medicine because they need to refer to separate columns each time. This has plusses and minuses - on the one hand, it makes your setup work harder. On the other hand, it avoids a lot of potential ways to run into problems ONCE it is setup. But, be aware of it - you can't just copy your narcan formula into your ketamine column and call it a day - you'll need to delete the {dataSource} references and add new ones (DON'T click the "Edit reference" button or whatever it is called - that will change it everywhere it was used and break the columns you've finished. Just delete it and add a new reference) to the correct columns.