Forum Discussion
Multiple Inventory entries on the same item
Okay so I'm wondering if there is a way for excel to automatically advance to the next column when entering inventory data so if I need to make another entry on the same item I don't have to advance to the next cell myself. This would be helpful in streamlining inventory and making it foolproof when adding inventory to a specific item (without having to worry about deleting previously entered data). I'm hoping there is a way to do this so I can take a tablet when completing my inventory and avoid the old clipboard and paper.
2 Replies
I find myself wondering how the whole spreadsheet is arranged, how you're walking around checking inventory and making entries on how many different items...
It sounds as if you've got one row per item,
- and are entering numbers for Item A each time you see ItemA on a shelf, then
- going to the row with ItemB and entering numbers for it,
- then back to ItemA and now needing to jump ahead to column whatever....
All of which might make sense if you've only got three or four items and no more than three or four of each in stock. But it only kinda makes sense.
Another approach would be something along the lines of this hastily created workbook. One sheet (Catalog) contains a list of current products, used to provide the drop down for column A of the inventory sheet itself.
You just walk around creating a new row for each new entry. If there are more entries needed than the current table provides, copy the last empty row before filling it, adding enough to meet the need. The Pivot Table takes care of totalling up the numbers of each.
This could be made more sophisticated, as needed. For example, if you want to track what room or shelf where each item is located, that could be another column, also managed by drop down so spelling is always consistent, etc.
See the attached spreadsheet.
Instead of pressing [Enter] to validate the item entry, press [Tab] (validate + move to the next column)
