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.

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.

.. 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.