Filtered data formula question

**lilsnoop** · 11-19-2011, 04:40 PM

Hello,

I'm not sure if this question would be better suited in the programming thread or not, but I was hoping there would be a formula to do what I need rather than vba coding.

I'm attaching a sample workbook as an small example:

In this sample workbook, I have two sheets/tabs. Ideally I want to filter by an individual name on the "Yearly statistics" tab, which will then allow me to see that single person's individual data. Once this filtering process takes place I would like (via a formula if possible, but vba would suffice if no other way) to have the 1st quarter data for the filtered individual to post in the appropriate summary cells, etc. The 2nd quarter results in that appropriate designated field and so on...

So if one were to filter "Jane Doe" on the yearly statistic tab; the number "19" would show up in the traffic enforcement cell on the 1st quarter summary cell G2 and "11" would show up in the self initiated arrest cell G3, etc...If data in the 2nd quarter columns, it would then show in the 2nd quarter form below the 1st quarter, etc. I hope this makes sense. Any assistance would be greatly appreciated!

**mrice** · 11-19-2011, 07:01 PM

Try this macro

Please Login or Register  to view this content.

I've just added the code for one quarter's first item, I'll leave you to add the rest.

**darkyam** · 11-19-2011, 07:01 PM

This formula in G2 should work: =INDEX('Year Statistics'!$2:$4,MATCH('Year Statistics'!$C$1,'Year Statistics'!$B:$B,0)-1,MATCH(A2,'Year Statistics'!$1:$1,0)+1).

Two notes:
1. You will have to change the titles on the second page to exactly match the first page. So A4 will have to be changed to Alcohol Related Arrests: for it to work.
2. To get this formula working for the other sections, change the +1 to +2, +3, or +4.

**darkyam** · 11-19-2011, 07:18 PM

Shoot, sorry, forgot to tell you I added a helper column and a formula in the hidden one you had. I'll just post my workbook.

**lilsnoop** · 11-19-2011, 07:23 PM

Thanks to both of you for looking at this problem for me. I've tried both of your solutions without success and I'm sure it is probably something I'm doing wrong. Any chance you could edit my original sample workbook file to reflect what you did and attach it back? That would allow me to figure out what I'm doing wrong. Thanks again for your time!