Creating an overdue report.
 

I manage a library of a couple thousand documents. I check these out to
people daily. I would like to have a report that shows which documents have
been checked out more then 30 days. Right now I have a column that shows the
date checked out. In the next column I use this formula,
=IF(P2<=TODAY()-30,"DUE","") this works fine to tell me when its due, but to
get a report out of it I have to sort on the "checked out date" then cut and
past that into a new document. Can someone show me how to come up with and
report that would give me this information at the press of a button, or two?
Thank you.

Creating an overdue report.
 

Have you tried the macro recorder while you go through the steps?

"I have to sort on the "checked out date" then cut and
past that into a new document"


Gord Dibben MS Excel MVP

On Wed, 30 Sep 2009 13:07:02 -0700, AVCM
wrote:

I manage a library of a couple thousand documents. I check these out to
people daily. I would like to have a report that shows which documents have
been checked out more then 30 days. Right now I have a column that shows the
date checked out. In the next column I use this formula,
=IF(P2<=TODAY()-30,"DUE","") this works fine to tell me when its due, but to
get a report out of it I have to sort on the "checked out date" then cut and
past that into a new document. Can someone show me how to come up with and
report that would give me this information at the press of a button, or two?
Thank you.

Creating an overdue report.
 

.. show me how to come up with report .. at the press of a button, or two?

Here's a simple formulas play where there's no need to press any buttons ..
It'll extract the overdues report automatically in a new sheet

Assuming your source table is in Sheet1, cols A to P, data from row2 down,
where col P houses the dates checked out (as per your post)

In a new sheet,
Put in A2: =IF(Sheet1!P2="","",IF(Sheet1!P2<=TODAY()-30,ROW(),""))
Copy A2 down all the way to cover the max expected extent of source dates in
Sheet1's col P. Hide away/minimize this criteria col A

Put in B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SM ALL($A:$A,ROWS($1:1))))
Copy B2 to Q2, fill down by the smallest* extent large enough to cater for
the max expected no. of overdue lines per day, say down to Q50? Cols B to Q
will return the overdue lines from Sheet1, all neatly packed at the top.
Format to taste.
*this will optimize recalc-performance as the extract formula is
calc-intensive

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"AVCM" wrote:
I manage a library of a couple thousand documents. I check these out to
people daily. I would like to have a report that shows which documents have
been checked out more then 30 days. Right now I have a column that shows the
date checked out. In the next column I use this formula,
=IF(P2<=TODAY()-30,"DUE","") this works fine to tell me when its due, but to
get a report out of it I have to sort on the "checked out date" then cut and
past that into a new document. Can someone show me how to come up with and
report that would give me this information at the press of a button, or two?
Thank you.