List only oldest 5 (or 10) open complaints

**Shocked** · 11-04-2009, 04:16 PM

How would I go about creating a summary of the oldest complaints.

I've posted versions of my complaints log/database here recently and am looking at the next version in reports.

I would like to create a small table that upper management can look at weekly and say "these are still open, why?" rather than having to look thru the log or go by memory.

I already have an "at a glance" chart, now I'd like to have something similar as a list (complaint number, date opened, customer, and maybe the issue at hand). would love to have this as a floating object that can be dragged around if needed. (embedded in a text box?)

**19fortysix** · 11-07-2009, 10:14 AM

Shocked,

A sheet has been added to the attached workbook and the "open" issues added to this sheet. The camera tool was then used to display the list on the Log sheet.

Hope this helps,
Gary

**Shocked** · 11-09-2009, 11:00 AM

sweet!

This is exactly what I was looking to do.

Thanks, so much.

**Jogier505** · 11-09-2009, 02:58 PM

19fortysix,

While it's not my thread I am at a loss to understand how it works.
How does the ,=MATCH("open",Status,0), formula arrive at row 271 from the Log Sheet? As well as the other formulas on Sheet 1?

Jim O

**19fortysix** · 11-09-2009, 03:25 PM

The formula in Column AA on sheet Log was added to determine if the issue was “not closed”. The range ($AA$3:$AA$325) was named “Status”.

The MATCH formula on Sheet1 cell A2 finds the row of the first occurrence of “open” in range “Status”. The formula in A3 Sheet1 finds the next occurrence of “open” or displays a blank cell if “open” is not found after the previous occurrence. This is repeated through A11.

The INDEX formulas in columns B, C and D simply use the row number returned in column A to display the requested data or a blank if no row number is found in column A.

If you would like a more detailed explanation of the formulas I would be happy to provide it just let me know but it will have to be done at a later time….I’m at work right now.

Gary

**Jogier505** · 11-09-2009, 04:40 PM

19fortysix,

I saw this post and how the concept would work for an issue I had but did'nt see the formulas in column AA.

Thank You for your time I have it working now.

Jim O