Dynamic Table of Store/Bank with location assignment (Dynamic Inventory)

Dear All

We are working in Embryo/Sperm Bank

We are using excel sheet to add patients stocks of embryos or Eggs or Embryos

These items stored in tubes/Straws

We are adding each patient in excel sheet, table as follow:

Patient-ID Name Civil-ID Freezing-ID Location Number of Tubes/Straws
223344 Michlke 33559988 S-335 T12C3V15 3
889951 Jessil 99668855 E-358 T2C10V23 2
.
.
.
.

this table ID/Location represents the Tank (Cupboard) and each Tank contains 10 Canisters (Box/Drawer) and each one composed of 30 Visitube (Track/Location)
I need to make summary/Dynamic table it can read location of each patient
based on its canisters and its visitube

SO in one table it can read from patient data' sheet all this summary
how many tubes/straws in each each visitube.
the values will fluctuate based on patient data addition or utilization

so from one table we can see which location is empty
and how many tube/straws are there
and if possible to show the Patient's ID

Can I get help here??

https://www.excelforum.com/attachmen...xcelforum.xlsb


thanks all in advance