Inventory Management Using Tables in Single Excel Sheet

How to create an inventory management system using tables in a single Excel worksheet. Last time we learnt how to create an inventory solution using tables. We used four worksheets called itemslist, received  (goods), issued (items) and stock (in hand). But tables are dynamic and flexible objects. We can use four separate tables in a single worksheet to find an inventory solution. Watch the video below:

The formulas used in the worksheet are as follows:

For Received Goods (table2):

to get description from the Items List (Table1): =VLOOKUP(G4,Table1,2,FALSE)

to get the unit from Items List (Table1): =VLOOKUP(G4,Table1,3,FALSE)

For Issued goods (Table 3):

to get description from the Items List (Table1): =VLOOKUP(G4,Table1,2,FALSE)

to get the unit from Items List (Table1): =VLOOKUP(G4,Table1,3,FALSE)

For Stock (Table 4):

to get description from the Items List (Table1): =VLOOKUP(G4,Table1,2,FALSE)

to get the unit from Items List (Table1): =VLOOKUP(G4,Table1,3,FALSE)

to get Total Quantity Received: =SUMIF(Table2[Sr No],[@[Sr No]],Table2[Qty Received])

to get Total Quantity Issued: =SUMIF(Table3[Sr No],[@[Sr No]],Table3[Qty Issued])

 

Further Reading:

10 reasons to use Excel’s table object

 

Leave a Reply

Your email address will not be published. Required fields are marked *