Jtdylktuy

I am working on a spreadsheet (I am doing it in Numbers since it will easily let me work on the file on my mac, iPhone, iPad and online at icloud.com)... but this is essentially also an excel question since either program could be used for the solution I am after. Just thought I'd start off with that though - since I believe Numbers doesn't support array formulas - not sure if I need one or not.

I have been trying to come up with a good way to keep track of some items that I check in and check out.

Its about 60 items (so far, and hopefully growing).

What I have been doing is using Numbers to check them in and out, but I'm hoping to automate it somewhat. So each time I check something out, I record it's item #, and also the name of who checked it out, along with the date they checked it out. I then have a column to record the date it was checked back in, and I enter that date when it does get back to me.

So my columns look like this:

A | B | C | D | E

Item # | Checked out to | Date Checked out | Date Checked in | Amount of days checked out

In a second sheet, I would like to automatically populate a list of only the available items for checkout, and have them sorted with the oldest item returned at the top, since it's the next one that should be checked out since it hasn't been for a while.

In the end it would look like this:

A | B | C | D

Item # | Last Checked out by | Last Date Checked in

So what should happen is that the "available for checkout" list only shows items that have been checked back in (that have a date listed under the Check In column). This gets complicated because my first sheet will end up displaying the same item many times as they get checked out and back in multiple times - so I only want it to list the most recent time it was checked back in.

In the end, I can then look at the list of available items, and know which one to check out next. And then when i enter it as checked out on my first sheet, it will automatically disappear from the "Available" sheet (until it's checked back in, and shows up at the end of the list).

I am thinking that once I get this working right, I'll easily be able to make the next auto populating sheet I need, which only lists items that are checked out for a really long period of time (like 3 months), since they kind of work in the same way, but with the opposite data.

A | B | C | D

Item # | Last Checked out by | How many days checked out

Any help would be so greatly appreciated!

Thanks for any help or ideas in advance!

You can do a lot (but not everything) with pivot tables. I made a basic setup in Excel for you, which should help you get started. One direction you should explore further is not to put every query (information need) in one pivot table, but to make a separate pivot table for each query you have.

To summarize what's in the Excel sheet (in case the Dropbox link breaks):

• Worksheet "Loan data" with a table, containing the following columns:
  
  
  
  
  • Item
  
  
  • Book checked out
  
  
  • Date checked out
  
  
  • Date checked in
  
  
  • Days checked out, with the formula: =IF(ISNUMBER([Date checked in]);INT([Date checked in]-[Date checked out]);"")
    
  
  
  
  


• Worksheet "Dashboard" with two pivot tables, both based on the "Loan data" table:
  
  
  
  
  • Last checkouts, with:
    
    
    
    
    • 
      Item as row label
    
    
    • 
      Date checked out as value, set to show the maximum value in date number format.
    
    
    
    
  
  
  • Current loan periods (blank if book is in stock), with:
    
    
    
    
    • 
      Days checked out as row label (sub/grand totals disabled)
    
    
    • 
      Item as second row label
    
    
    • 
      Date checked out as value, set to show the maximum value in date number format, showing the last checkout date.
    
    
    • 
      Date checked in as value, set to show the maximum value in date number format, showing the last checkin date.