Setting up an Inventory Management Process

Riza Khan

I do a daily count of inventory in a storage room and submit the count of each item via a form that feeds into a central sheet for each location. I'll refer to this at the "COUNT" sheet.

Each item has it's own form, for example "Gloves". This means each day there are new row for "Gloves" with the most up to date count in column B.

Since I record one item per form, I have 10 new lines a day on the COUNT sheet.

On another sheet - I have a list of the same items with the minimum level required at each location. I'll refer to this at the "PAR LIST". The PAR List contains all the same columns at the Count sheets.

How can I marry these two sheets together and present the data in a way that shows the most recent COUNT vs. the PAR list? From there I can create what I need to make this data useful.

Werner Gerstacker

Take a look at the attached screenshot - prerequisite: there is exactly one entry per day and item in the COUNT sheet.

200522_Question_Inventory_List.jpg

The dates in the PAR List sheet don't matter.

Also:

This only works for today - if you don't have new entries tomorrow an you look at the 'COUNT vs PAR' sheet it will not find anything. By manually entering a date in the cell '[Date]1' and using the formula =[Date]1 in rows 2 and 3 you can get around this, but it will not update automatically every day.

Note: Depending on how you want to e.g. chart the COUNT vs. PAR table, you might want to swap rows and columns - the formulae would stay the same on principle.