Autofilter, Index/Match or Lookup? Displaying all results of multiple criteria.

[JonHaywood]

Hi,

I'm trying to create what would effectively be a summary sheet so that different users can see information relevant to them within a date range.

All of the data is held on a sheet called "Data" in a separate workbook; it looks like this...(assume reference A1:E11)

Comment #	Date	Raised By	Details	Location
9KPFDT	02/06/14	A Person	(free text string 1)	Top Stores
9KPFW2	02/06/14	B Good	(free text string 2)	Sector 11
9KQ9XT	02/06/14	C Clearly	(free text string 3)	Sector 10
9KQ9YY	03/06/14	D Liteful	(free text string 4)	Sector 30
9KQFNC	03/06/14	B Good	(free text string 5)	Lower Stores
9KQFQ5	03/06/14	A Person	(free text string 6)	Sector 11
9KQH3C	03/06/14	E Ventful	(free text string 7)	Sector 42
9KQH4L	03/06/14	B Good	(free text string 8)
9KQJFC	03/06/14	C Clearly	(free text string 9)	Garage
9KQJQK	03/06/14	B Good	(free text string 10)	Sector 41

<tbody>
</tbody>

(note: the empty cell in E9 is deliberate)




Some of the users are responsible for some of the different areas. I have another worksheet called "Locations", like this...(assume reference A1:B11)

User	Location
B Good	Sector 10
B Good	Sector 11
B Good	Sector 12
C Clearly	Sector 30
C Clearly	Sector 31
C Clearly	Garage
E Ventful	Sector 41
E Ventful	Sector 42
E Ventful	Top Stores
E Ventful	Lower Stores

<tbody>
</tbody>

The "Summary Sheet" then, would look like...(again, assume reference A1:E100)

User Name	(dropdown list)
Date From
Date To
Location	Comment #	Date	Raised By	Details

<tbody>
</tbody>

I would prefer if it was sorted by location, then comment (as it is generated sequentially). So, an example would be...

User Name	B Good
Date From	02/06/14
Date To	03/06/14
Location	Comment #	Date	Raised By	Details
Sector 10	9KQ9XT	02/06/14	C Clearly	(text string 3)
Sector 11	9KPFW2	02/06/14	B Good	(text string 2)
Sector 11	9KQFQ5	03/06/14	A Person	(text string 6)

<tbody>
</tbody>

I've tried lots of different things as suggested on lots of different posts, but I can't make anything that match what I want.

Hope someone can help!

 

[JonHaywood]

I've looked at this differently and have managed (annoyingly easily by just adding a extra 'helper' column to my original data!!) to link the first two spreadsheets, and have created a formula to display the 'filtered' data in the final sheet. I'm still struggling to sort the data how I want though. If I can't manage this I will post a new question on the board.
Thanks to anyone who looked at this.

 

[JonHaywood]

I've posted a new question to the forum for help with sorting the results http://www.mrexcel.com/forum/excel-...a-visual-basic-applications-paste-values.html