Inventory Sheet (Search Multiple Sheets) Help!

[Fxguy]

I'm trying to create a workbook for a co-worker to use for tracking inventory. Let me start with a little bit of what I am trying to do and also state that this would probably be much easier to create using access as a database than in excel, but this coworker is access challenged and so she really needs it to be in excel if at all possible.

Ok, so we are an inpatient pharmacy and we are trying to keep track of several medications that we have in various locations for the purpose of inventory. Each med has a unique NDC number assigned to it so we want the totals to be specific to each NDC. However, each NDC could be assigned to multiple locations and she wants a sheet for each location.

An example....
Lets say we have Sodium Chloride 0.9%, 20 ml vials.
The NDC is 00409-4888-20.
We stock 20 vials in supplemental crash cart boxes, 10 vials in Epidural Boxes, and 62 in Crash Carts throughout the hospital.
So we have a Master Inventroy Sheet that has the following columns set up:
NDC Number - Box - MNEMONIC - QTY IN BOX - Number of Boxes- ITEM Desc - Total QTY

The Data is grouped together by NDC so each box underneath is a seperate line with a Summary Line Below. There is also an individual worksheet with the same columns for each unique location.

What my coworker wants is the Master Inventory Sheet but to have the ability of the Master Inventory sheet to update if she changes a qty in a location or to add or delete items in particular locations.

So any ideas? Please Help! I'm currently doing this manualy and it is a mess / taking forever!

Thanks for the advice and thoughts!

 

[bschwartz]

I would make one spreadsheet for inventory transactions (receipts, adjustments, withdrawals, etc), and one (or more) summary page(s).

For example (most of these fields would be calculated or lookups, and could be hidden, or even removed):

Date	NDC	Box	MNEMONIC	QTY IN BOX	Number of Boxes	ITEM Desc	Total QTY
1/5/2013	00409-4888-21	Supplemental Crash Cart	Example Item 2	12	-57	Example Item 2	-684
2/6/2013	00409-4888-22	Crash Cart	Example Item 3	10	124	Example Item 3	1240
2/25/2013	00409-4888-20	Crash Cart	Example Item 1	15	-85	Example Item 1	-1275
3/12/2013	00409-4888-23	Supplemental Crash Cart	Sodium Chloride 0.9%, 20 ml	10	-66	Sodium Chloride 0.9%, 20 ml	-660
3/29/2013	00409-4888-23	Supplemental Crash Cart	Sodium Chloride 0.9%, 20 ml	10	-67	Sodium Chloride 0.9%, 20 ml	-670
5/6/2013	00409-4888-20	Crash Cart	Example Item 1	15	-8	Example Item 1	-120
5/19/2013	00409-4888-20	Supplemental Crash Cart	Example Item 1	15	86	Example Item 1	1290
5/26/2013	00409-4888-23	Epidural	Sodium Chloride 0.9%, 20 ml	10	87	Sodium Chloride 0.9%, 20 ml	870
5/28/2013	00409-4888-20	Epidural	Example Item 1	15	88	Example Item 1	1320
6/8/2013	00409-4888-22	Epidural	Example Item 3	10	143	Example Item 3	1430
6/21/2013	00409-4888-22	Supplemental Crash Cart	Example Item 3	10	13	Example Item 3	130
6/24/2013	00409-4888-23	Epidural	Sodium Chloride 0.9%, 20 ml	10	1	Sodium Chloride 0.9%, 20 ml	10
7/2/2013	00409-4888-20	Crash Cart	Example Item 1	15	126	Example Item 1	1890
7/22/2013	00409-4888-22	Epidural	Example Item 3	10	75	Example Item 3	750
8/4/2013	00409-4888-22	Crash Cart	Example Item 3	10	89	Example Item 3	890
8/7/2013	00409-4888-22	Supplemental Crash Cart	Example Item 3	10	-146	Example Item 3	-1460
8/21/2013	00409-4888-22	Crash Cart	Example Item 3	10	78	Example Item 3	780
9/1/2013	00409-4888-23	Supplemental Crash Cart	Sodium Chloride 0.9%, 20 ml	10	110	Sodium Chloride 0.9%, 20 ml	1100
9/14/2013	00409-4888-23	Supplemental Crash Cart	Sodium Chloride 0.9%, 20 ml	10	-121	Sodium Chloride 0.9%, 20 ml	-1210
9/23/2013	00409-4888-20	Supplemental Crash Cart	Example Item 1	15	18	Example Item 1	270
10/1/2013	00409-4888-20	Epidural	Example Item 1	15	-46	Example Item 1	-690
10/5/2013	00409-4888-23	Crash Cart	Sodium Chloride 0.9%, 20 ml	10	-93	Sodium Chloride 0.9%, 20 ml	-930

<tbody>
</tbody>

and then a summary page:

	ON HAND INVENTORY	Epidural	Supplemental Crash Cart	Crash Cart
00409-4888-20	Example Item 1	630	1560	495
00409-4888-21	Example Item 2	0	-684	0
00409-4888-22	Example Item 3	2180	-1330	2910
00409-4888-23	Sodium Chloride 0.9%, 20 ml	880	-1440	-930

<tbody>
</tbody>

Formula: =SUMIFS('Inventory Trans.'!$H$2:$H$1000,'Inventory Trans.'!$B$2:$B$1000,$A2,'Inventory Trans.'!$C$2:$C$1000,C$1)

Unfortunately, I can't get my Mr Excel HTML maker to work properly, so it's not showing up great.