Suggestions please!

[Sarah_Lecturer]

Dear all

I am looking for suggestions from anyone who can help. My company have
asked me to design a spreadsheet which tracks pool laptops which we have
available for loan. Currently this is on one sheet in a workbook and is
somewhat confusing. The main issue with the current system is that it does
not hold historical information as to who had the laptop on what date and
when it was returned. We are not allowed to use Access to build this
"database" it has to be Excel.

So, in essence, we have 24 laptops, we have 24 Check sheets that correspond
to each laptop and we want to be able to "press a button" to send an email to
users as a reminder when their laptops are overdue. As well as being able to
view historical information at any time and or produce a report accordingly.

I have a few ideas about how to go about this but was hoping that all of you
could maybe assist me and perhaps offer some better suggestions. I started
thinking about some form of summary sheet with a pivot table so that laptops
which are available could be viewed at a glance and had some ideas abouts
multiple sheets etc. We are using Excel 2003.

I would be very interested to hear your ideas and suggestions and thanks in
advance for your help

Sarah x

 

[Pete_UK]

I think it may be easier to have just one sheet for the loan_details
which will track all the laptops, rather than 24 separate sheets one
per laptop. In addition to this, you will need a sheet for registered
users (where you can keep all their details in one place and have a
unique ID for each user), and another sheet where you can list all the
details about each laptop. This makes it easy to add new users and
laptops.

The loan_details sheet will have columns for date, laptop_ID, User_ID,
Action (i.e. borrowed or returned), together with other details that
you feel are necessary. The laptop_ID and User_ID fields can be
populated via drop-downs linked to their appropriate sheets.

From this basic set up you can then have different reporting sheets to
show you a list of available laptops, overdue laptops etc., rather
like a small library system.

Hope this helps.

Pete

 

[Sarah_Lecturer]

This sounds like a plan Pete - much appreciated.... no doubt I will be back
to query the parts I am unsure of!

Thanks again

Sarah