Tool inventory

**GMImanager123** · 02-10-2011, 10:02 AM

So I am working on an assignment for work, and what I am trying to do with excel is something i've never done before. i work for a contracting company (constructioN). I am now in charge of all tools and equipment within my company. There are hundreds, almost thousands of units that my company owns. I need to create a spreadsheet that can help me track all these items.

My company has around 50-80 projects going on at any given time. With 6-8 superintendants, running anywhere from 1-10 jobs each, we are starting to lose track of our equipment. When a piece of equipment is needed, the superintendant of the job will come to me and tell me what he needs, and i will need to be able to tell him if one of the other superintendants has the tool, or if it is in our warehouse, or if we need to buy another because all other tools are being used.

Basically, what i need to create is similar to the type of software that any rental company would have. like blockbuster video, for example.

I want to have a tab that has the company's total pool of equipment, showing ALL equipment with ALL quantities. Then maybe on a different tab, or further down that page, i want to have drop down boxes where i can assign a quantity to a particular superintendant. When i assign a piece of equipment to person, i want it to automatically take that qty. out of the company's pool qty. Could someone explain to me how this can be done, or at least point me in the right direction? I don't even know what to look up as far as tutorials for this situation. Help would be much appreciated

**royUK** · 02-10-2011, 10:38 AM

Make a start by designing your spreadsheet. Then post back and attach it so that we have something to work with

**Cutter** · 02-10-2011, 10:57 AM

You might get some ideas from here:

http://office.microsoft.com/en-ca/te...010117254.aspx

**MarvinP** · 02-10-2011, 02:56 PM

Hi GMImanager123 and welcome to this forum.

Your question is a GREAT one and has me spending lots of time remembering history and examples that apply to this problem. You ask this question here because Excel is your tool of choice to answer this question. Your question (for today) is "Where is all that stuff?"

BEWARE the questions change with time and software tools. Tomorrow it may be; when do we need to repair it, when will it be available for another job, do we need to buy more of them. is it on the Crital Path, or how much is this job going to cost. Here is the graveyard for software trying to answer these past and harder questions. http://en.wikipedia.org/wiki/List_of...ement_software

Excel has survived the above list because it has grown with versatility. You must be able to pull in your inventory list from some other product. Excel has a huge set of import options. You need to start with your STUFF list and import it. Don't type it in yourself. This will be a start. Your idea is to answer the question not do data entry.

You need a List of all Locations the Stuff can be. Start with HOME (in your wherehouse) and then list all other project locations. Then add a field of Who checked it out or the list of project managers. Date is always a good field to include in an inventory database too.

Now - I'm imagining a single long table that has these fields..

Stuff, Location, Checked out by, Date (this list can grow but only if necessary)

You start with all Stuff and don't want to enter every porta-potty by serial number so you need a number in the above list. Now here is my delema and must guide the process. All stuff starts at home with a PLUS sign on how many. When it moves to a site it gets a minus sign. It MUST be checked back to the shop/home before being checked out again. This requires a Location and the correct plus/negative sign on the quantity. The reason for needing to be checked in again is to keep the totals correct. I'm imagining a porta potty going from Loctation 1 to 2 without being logged back to home. In this case the numbers won't work.

I've built a short example for you to view - to get an idea of what I'd do. See attached.
I hope this gives you some ideas. Good Luck - you have a tough job.