Creating an overdue report.

AVCM · External Since: Sep 30, 2009 Posts: 1

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.

Gord Dibben · External Since: Feb 23, 2004 Posts: 9178

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 <AVCM RemoveThis @discussions.microsoft.com>
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.

Max · External Since: Mar 17, 2004 Posts: 1751

> .. 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,SMALL($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.