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)

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Comment #[/TD]
[TD]Date[/TD]
[TD]Raised By[/TD]
[TD]Details[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]9KPFDT[/TD]
[TD]02/06/14[/TD]
[TD]A Person[/TD]
[TD](free text string 1)[/TD]
[TD]Top Stores[/TD]
[/TR]
[TR]
[TD]9KPFW2[/TD]
[TD]02/06/14[/TD]
[TD]B Good[/TD]
[TD](free text string 2)[/TD]
[TD]Sector 11[/TD]
[/TR]
[TR]
[TD]9KQ9XT[/TD]
[TD]02/06/14[/TD]
[TD]C Clearly[/TD]
[TD](free text string 3)[/TD]
[TD]Sector 10[/TD]
[/TR]
[TR]
[TD]9KQ9YY[/TD]
[TD]03/06/14[/TD]
[TD]D Liteful[/TD]
[TD](free text string 4)[/TD]
[TD]Sector 30[/TD]
[/TR]
[TR]
[TD]9KQFNC[/TD]
[TD]03/06/14[/TD]
[TD]B Good[/TD]
[TD](free text string 5)[/TD]
[TD]Lower Stores[/TD]
[/TR]
[TR]
[TD]9KQFQ5[/TD]
[TD]03/06/14[/TD]
[TD]A Person[/TD]
[TD](free text string 6)[/TD]
[TD]Sector 11[/TD]
[/TR]
[TR]
[TD]9KQH3C[/TD]
[TD]03/06/14[/TD]
[TD]E Ventful[/TD]
[TD](free text string 7)[/TD]
[TD]Sector 42[/TD]
[/TR]
[TR]
[TD]9KQH4L[/TD]
[TD]03/06/14[/TD]
[TD]B Good[/TD]
[TD](free text string 8)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9KQJFC[/TD]
[TD]03/06/14[/TD]
[TD]C Clearly[/TD]
[TD](free text string 9)[/TD]
[TD]Garage[/TD]
[/TR]
[TR]
[TD]9KQJQK[/TD]
[TD]03/06/14[/TD]
[TD]B Good[/TD]
[TD](free text string 10)[/TD]
[TD]Sector 41[/TD]
[/TR]
</tbody>[/TABLE]












(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)
[TABLE="width: 500"]
<tbody>[TR]
[TD]User[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]B Good[/TD]
[TD]Sector 10[/TD]
[/TR]
[TR]
[TD]B Good[/TD]
[TD]Sector 11[/TD]
[/TR]
[TR]
[TD]B Good[/TD]
[TD]Sector 12[/TD]
[/TR]
[TR]
[TD]C Clearly[/TD]
[TD]Sector 30[/TD]
[/TR]
[TR]
[TD]C Clearly[/TD]
[TD]Sector 31[/TD]
[/TR]
[TR]
[TD]C Clearly[/TD]
[TD]Garage[/TD]
[/TR]
[TR]
[TD]E Ventful[/TD]
[TD]Sector 41[/TD]
[/TR]
[TR]
[TD]E Ventful[/TD]
[TD]Sector 42[/TD]
[/TR]
[TR]
[TD]E Ventful[/TD]
[TD]Top Stores[/TD]
[/TR]
[TR]
[TD]E Ventful[/TD]
[TD]Lower Stores[/TD]
[/TR]
</tbody>[/TABLE]


The "Summary Sheet" then, would look like...(again, assume reference A1:E100)
[TABLE="width: 500"]
<tbody>[TR]
[TD]User Name[/TD]
[TD](dropdown list)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date From[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date To[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]Comment #[/TD]
[TD]Date[/TD]
[TD]Raised By[/TD]
[TD]Details[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would prefer if it was sorted by location, then comment (as it is generated sequentially). So, an example would be...
[TABLE="width: 500"]

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

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