Inventory Tracking Help

%3CLINGO-SUB%20id%3D%22lingo-sub-292946%22%20slang%3D%22en-US%22%3EInventory%20Tracking%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-292946%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20in%20need%20of%20a%20formula%20to%26nbsp%3Bautomatically%20display%20the%20current%20inventory%26nbsp%3Bstatus%20of%20kits.%26nbsp%3B%20It%20will%20need%20to%20show%20if%20it%20is%20in%20use%2C%20or%20if%20it%20has%20already%20been%20returned%20to%20my%20facility%2C%20plus%20just%20a%20few%20other%20items%20such%20as%20the%20clients%20name%2C%20expected%20return%20date%2C%20etc.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20I'm%20having%20is%20that%20it%20will%20need%20to%20ignore%20responses%20(blank%20cell%20as%20a%20response)%20if%20the%20kit%20has%20already%20been%20received%2C%20or%20isn't%20in%20use%2C%20which%20will%20continue%20through%20a%20year.%26nbsp%3B%20We%20may%20have%201000%20rows%20(patients)%20through%20a%20year%3F%26nbsp%3B%20Basically%20it%20will%20tell%20me%20if%20it's%20ready%20to%20be%20given%20to%20another%20patient%2C%20or%20if%20it's%20in%20my%20hands.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%26nbsp%3Bwhere%20the%20data%20is%20pulling%20from%20(one%20worksheet%2C%20multi%20tabs)%3A%26nbsp%3B%20%22Scheduled%22%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F61011i9FBDE3F2360EB01A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22ScheduledTab.JPG%22%20title%3D%22ScheduledTab.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20this%20is%20the%20information%20I%20would%20like%20to%20automatically%20update%3A%26nbsp%3B%20%22Equipment%20Tracking%22%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F61013iE8F991AF2AFC7F7E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22EquipmentTrackingTab.JPG%22%20title%3D%22EquipmentTrackingTab.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20ready%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-292946%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2350323%22%20slang%3D%22en-US%22%3ERe%3A%20Inventory%20Tracking%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2350323%22%20slang%3D%22en-US%22%3EPut%20this%20formula%20in%20C16%20of%20the%20Equipment%20Tracking%20Table%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(ISERROR(INDEX(B16%3AEND%20OF%20SCHEDULED%20TABLE%20RANGE%20(BOTTOM%20RIGHT%2CMATCH(B16%2CAA15%3AAA100%2C0)%2C27))%2C%22INVENTORY%22%2CINDEX(ALL%20SCHEDULE%20TABLE%20RANGE%2CMATCH(B16%2CAA15%3AAA100%2C0)%2C29))%3CBR%20%2F%3E%3CBR%20%2F%3EI%20see%20merged%20cells%20in%20the%20Scheduled%20Table%2C%20hopefully%20that%20does%20not%20create%20a%20problem%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

 

I'm in need of a formula to automatically display the current inventory status of kits.  It will need to show if it is in use, or if it has already been returned to my facility, plus just a few other items such as the clients name, expected return date, etc.  

 

The problem I'm having is that it will need to ignore responses (blank cell as a response) if the kit has already been received, or isn't in use, which will continue through a year.  We may have 1000 rows (patients) through a year?  Basically it will tell me if it's ready to be given to another patient, or if it's in my hands.

 

Here is where the data is pulling from (one worksheet, multi tabs):  "Scheduled"

ScheduledTab.JPG

 

 

And this is the information I would like to automatically update:  "Equipment Tracking"EquipmentTrackingTab.JPG

 

 

I'm ready for your help!

 

 

 

1 Reply
Put this formula in C16 of the Equipment Tracking Table:

=IF(ISERROR(INDEX(B16:END OF SCHEDULED TABLE RANGE (BOTTOM RIGHT,MATCH(B16,AA15:AA100,0),27)),"INVENTORY",INDEX(ALL SCHEDULE TABLE RANGE,MATCH(B16,AA15:AA100,0),29))

I see merged cells in the Scheduled Table, hopefully that does not create a problem

Cheers