Inventory Control

Hello!

I am here again to share another solution I had to come up with to meet a need I had, but this time not with PowerShell, but with Excel.

The situation was this: I had to control the stock of 4 different locations, controlling several different types of devices, both new and used, and all in one place.

I know that there are a lot of software that do this, but I could not spend money, and we know that it is very difficult to find a software that fits you 100%. So, I decided to create a Excel file with five sheets, one for each location and one with a graphic and a table with a summary.

In the sheets of the locations, I created a table with all the information I needed separated into columns, which are:

• Hostname: To enter the hostname of the computer.
• Type: What kind of device it is? Can be a Desktop, Laptop, Monitor, Keyboard, etc.
• Brand: The brand of the equipment.
• Model: To enter the model of the device.
• Service Tag: To enter the Service Tag of the Desktop or Laptop. This is very useful for Dell and HP computer to check the warranty.
• Serial Number: To enter the serial number of the equipament.
• Patrimony: To enter the patrimony number of the equipament.
• Old / New: This field is to say if the device is new (in the box) or old (used).
• In Stock (Yes/No): Here we will inform if the equipment is in stock or not. Here is how it works. The device is in stock (Yes), and you deliver it to a new employee. So now the status will change to "No". You don't delete the line when you deliver a device, you just change the status, because in the future, this device can come back to the stock.
• Reserved for: This field is used to tell to whom a device is reserved for future deliver. The device is in stock, but you know that in two weeks from now you will have to deliver the device to a new Manager.
• Transfered to: Field used to say to which location you transfered a device. Maybe location A has 100 laptops, but location B has only 30. So, you do not have to buy new ones, you can transfer from location A to B and take note.
• Delivered With The Ticket: Field to enter the number of the ticket opened with the request to deliver/change the device.
• Name of the User: To enter the name of the user that received the equipament.

With these fields, I can control different types of devices and fill the necessary column.

Now, in the graphic sheet, we have the columns "Type", "Old / New" and "In Stock (Yes/No)", to create the data validation list used in the location spreadsheets.

We also have the "Status" column, used to create a PivotTable, which in turn is used to create a data segmentation control for the chart. Usually I hidden these columns.

Still in the graphic sheet, we also have tables with a summary of the numbers. These tables are also used to create the graphic itself.

Last, but not least, we have the graphic and the data segmentation control with the "Old", "New" and "Total" options. It is only one graphic to show three different information.

Below is the chart showing the Total.

The chart showing the Used.

And the chart showing the New.

That's it. Below is the link for you to download the spreadsheet if you wanted to use. Don't worry, it has no viruses and no macros.

Feel free to use, modify and share it if you wish. And if you decide to use the worksheet, do me a favor, come back here and leave a comment telling where are you from, and what do you think of it. ?

Bye!!

PS1: I'm not an expert in Excel.

PS2: I am not 100% fluent in English, so feel free to send me a message pointing out grammar errors, so I can learn and fix it.