• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

hard drive Tracker

V

vicmoment

Member
I manage about 20 staff members and have 40 hard drives which staff members collect from time to time and return when done. I want to be able to track all drives with the date it was collected, the staff and date of return if already returned and a status field saying (available, not yet returned).


The objective is to at a glance see all hard drives that are available so they can be collected to staff when in need of them (maybe a pivot table, not sure)


i know i would have to key in the information my self but whats the best way of setting up the excel sheet.


Eagerly awaiting responses from you all.


Thanks
 
I'd setup column headers like this:

Serial # | Name of Staff | Date check out | Date checked in


With those 4 manually entered fields, you can use formulas to determine status, count of status, who has how many, etc.
 
Thanks luke,I appreciate.


i was wndering how i could add something like a data validation to input the names, hard-drive name and date requested.


Could i have some cells that accept as input the hard drive name (A1), staff name (A2)and date requested(A3, maybe a calendar entry) and maybe an add button in such a way that when i click the add button, it adds the entry to the bottom of the already existing list i have.


i hope my question is clear
 
Sure thing. Check out Debra's template here, to get you started, or give you some ideas:

http://www.contextures.com/xlForm02.html
 
Thanks Luke,


You've been so helpful.


Her approach and macro work fine, but how can I get generate a summary wich always updates as more rcords are added.


I would have thought a pivot table but the source data would have to change every time and i wouldnt want to that. I probably would want just a refresh button to update te table.


Your ideas are much welcome
 
Why would the source data need to change each time? You can either define the source as entire columns, or use a dynamic named range.

In 2003, there was a toolbar button for 'Refresh all' that would refresh all PivotTables and queries in a workbook. If that's not available, you could make a command button tied to this macro:

[pre]
Code: 
Sub RefreshMe
ThisWorkbook.RefreshAll
EndSub
[/pre]
 
You must log in or register to reply here.
Back
Top