Stock report in Excel 2000 - naive user needs help

[NewBoy]

Hi,

Please excuse anything that follows which is stupid - I am not very good with Excel, and my method probably seems naive. However, I would appreciate help...

I've got router stock I need to keep track of. The stock is lent out on an as-needed basis, and is either returned, or replaced with an equivalent router (ie same model).

I'm logging all routers going out, and all routers coming in, in an Excel spreadsheet. The fields of importance are ENGINEER (whoever sends/receives the router) MODEL (of router) SERIAL (unique number for each router) ID (of customer) IN/OUT (depending on whether it's a loan or return).

What I want to do is be able to get a daily list of outstanding loans so I can chase up engineers. I could use Access - but I'm rubbish at using it. I could create a script of some sort - but I think that's a bit over the top. I understand Excel, and so I want to use it.

All I do is create a pivot table with ENGINEER in Rows, IN/OUT in Cloumns, and SERIAL as the data item you drop in the middle. Then I create a column on the side with a simple formula in each cell like '=OUT-IN' to determine what the net is (i.e. X routers out, or Y routers in).

This doesn't keep track of each individual transaction. So if I lend one router to Mary and get one back from George - it looks like I'm not owed any routers (but Mary still does).

Sorry for not explaining this well. And as I type I am well aware that I am probably doing this completely wrong. YOUR HELP WILL BE VERY MUCH APPRECIATED!!

Thanks.

 

[NewBoy]

And one other thing. When I delete a row from the worksheet containing the raw data, the pivot table is no longer valid.

Can I get around this?

Thanks :?)

 

[JVFriederick]

Hi NewBoy,

I would set up your table like this :
DATE OUT, DATE IN, ENGINEER, MODEL, SERIAL, CUSTOMER ID

Use the filter instead of the pivot table and filter for DATE IN < 1 to determine all routers still out on loan. Create a separate record for each &quot;event&quot;. This method will also allow you to track the history of each engineer, when routers were on loan, for how long, etc.

If you wish to automate each filter process, and create separate queries, this can be done with separate macros assigned to buttons.