Excel 2007 : Track Inventory

Hey all,

I am in no way an expert at using Excel. I can find my way around it for some really basic spreadsheets but I need some help in deciding what the best way is to keep track of specific inventory.

I'm working in IT and I need a way to keep track of a number of things. To start, I want to keep track of printers and their toner usage. I'd like to be able to specify when they toner was last changed or have some sort of quick "check box" to mark off which will automatically record when I change it and also possibly calculate the amount of time in between the last time it was replaced. If it's even possible, I'd like to see about setting up a formula to calculate the average length of time in between changes.

I don't know if this is all possible and I apologize if this isn't in the right forum. I could look up a template but I'd rather do this thing from scratch. Poking around it seems like this would be the sort of thing people would want to get paid for. I'm looking for a free solution. However, if that's not possible I would appreciate any help in getting pointed in the right direction.

In the future I'd like to add another spreadsheet to keep track of desktops or machines within the company. I'd need to log models/model #'s, date we purchased/acquired them, who it's currently assigned to, service tags if any, and any other pertinent information we deem necessary.

I realize this may be a tall order but I'm hoping I can start a more organized and clean spreadsheet. Thanks in advance for the help.

Thanks for the attached files. I'm looking at them right now and I'm already trying to figure out ways I can adapt this for my usage. Is it hard to add drop-down boxes and such? The user field isn't one I'd need so I can probably delete that entire row or find another use for it. Thanks again and hopefully I can find a way to augment this. I need to learn some serious excel skills.

Originally Posted by squiggler47

Average toner life is calculated from the log table, it basically takes the first date listed and the last date listed and subtracts them to find the number of days, then counts the number of changes and divides.

You may find that you need to change the formula to :-

=SUMPRODUCT(MAX('Toner Log'!$C$3:$C$6*(Printers!$B3='Toner Log'!$B$3:$B$6))-MIN('Toner Log'!$C$3:$C$6+(1E+304*(Printers!$B3='Toner Log'!$B$3:$B$6))))/MAX(COUNTIF('Toner Log'!$B$3:$B$6,Printers!$B3)-1,1)

It just needs you to enter the in service date as the first toner change date, that way you can track it!

Thanks, this formula seems to be working well as far as I can test it.

I ran into a problem when I tried expanding the cells it is calculating from. I've modified it to read from,

'toner log' B3 thru B12 and from C3 thru C12. My problem is on the toner log worksheet. How can I keep adding to this? I've tried simply dragging from the lower right corner down but the formula doesn't seem to like that.

Originally Posted by squiggler47

Can anyone with 2003 take a look at the file, unfortunately I dont have access to 2003 at the moment! I think its a problem with the ranges because of the tables, the formulas should change when the table is expanded!

I think you're right and I believe it is working. I don't know but I must have goofed something up when I was playing around with it. I think I'll take what you've given me here and run with it. If I have any more specific questions I'll post back here.

Hello, is there any excel format to track the usage of toners for the office and branches?

Thank you