Executive summary

[Perp]

I am using Excel Ver2003.
I have several tabs of data tracking customer complaints by year (2006,
2007, etc.). Each tab contains about 20 columns of the complaint specifics.
Each tab may contain up to 100 complaints. Some of the complaints are open,
some are closed. I have been tasked with generating an "Executive Summary"
showing all open complaints and their status. I would like to be able to
select a department and have all open complaints from that specific
department populate the summary tab.
On the Excutive Summary tab I created a pulldown list for the departments.
Using the pulldown value I can get the Hlookup to find and transfer the data
from the first occurrence of the departments complaints from one tab, but it
doesn't transfer the rest.

Sorry for the long winded description. I would be open to trying other
methods if they would be better suited.

Perp

 

[Max]

Here's a formulas driven model which delivers it ..

Illustrated in this sample:
http://freefilehosting.net/download/42m58
Exec Summary.xls

Construct:
Source data assumed in sheets named as eg: 2008, 2007, etc
data in cols A to F from row2 down,
with key cols B & C (col B = Dept, col C = Status, eg Open, Closed)

In the sheet: Exec Summary,
In A1 is a DV to select the source sheetname, eg: 2008, 2007
In A2 is a DV to select the Dept, eg: Dept1, Dept2

In B2
=IF(AND(INDIRECT("'"&$A$1&"'!B"&ROWS($1:1)+1)=$A$2,INDIRECT("'"&$A$1&"'!C"&ROWS($1:1)+1)="Open"),ROW(),"")
Leave B1 blank

In C2
=IF(ROWS($1:1)>COUNT($B:$B),"",INDEX(OFFSET(INDIRECT("'"&$A$1&"'!A:A"),,COLUMNS($A:A)-1),SMALL($B:$B,ROWS($1:1))))

Copy C2 to H2. Select B2:H2, fill down to cover the max expected extent of
source data, eg down to H200?. Minimize col B. Cols C to H will return the
required extracts, all neatly packed at the top, depending on the selections
made in the DVs in A1 and A2.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik

 

[Perp]

Max,
Thank you for your efforts. You were right on the money and it worked
perfectly the first time. Truely the best help I've ever received.

Perp

 

[Max]

Welcome. Could you press the YES button (like the one below) in that
response, pl?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik