JonHaywood
New Member
- Joined
- Jul 23, 2014
- Messages
- 34
I'm using an Index/Match array formula to pull out results from a data table (based on three search criteria). The formula works great, except that it populates the results in the same order as the data table - I want to sort them based on another category.
My data table is on Sheet 1...
<tbody>
</tbody>
(Note: The blank cells in A4 and B4 are deliberate)
I don't want users to have access to this main data table, but I do want different users to be able to select some search criteria, then view a summary of their data. I've created a summary table on Sheet 2...
<tbody>
</tbody>
The search criteria are entered by the user in cells B1 to B3.
In A6 (then copied across and down) I have the formula...
{=IF(ROWS(A$6:A6)<=(SUMPRODUCT(--(Sheet1!$A$2:$A$9=$B$1),--(Sheet1!$C$2:$C$9>=$B$2),--(Sheet1!$C$2:$C$9<=($B$3+0.999)))),INDEX(Sheet1!B$2:B$9,SMALL(IF((Sheet1!$A$2:$A$9=$B$1)*(Sheet1!$C$2:$C$9>=$B$2)*(Sheet1!$C$2:$C$9<=($B$3+0.999)),ROW(Sheet1!B$2:B$9)-ROW(Sheet1!B$2)+1),ROWS(A$6:A6))),"")}
So, if B1 = A Person, B2 = 01/06/14 and B3 = 03/06/14 the results I get are...
<tbody>
</tbody>
Is there a way to sort these results by location? I've tried all basic ways to sort them but no joy. I have looked at other posts where Autofilter has been suggested, but my VBA knowledge is weak and I can't figure out how to change any of the suggestions to fit my tables.
Alternatively, I'd be happy if there was a VBA routine to automatically copy and paste the values into another table which could be filtered and sorted by the user, but again, I can't figure out how to adapt the examples I've seen of this approach.
Help me Mr Excel forum... you're my only hope!!!
Thanks
My data table is on Sheet 1...
A | B | C | D | |
1 | T/L | Location | Date | Description |
2 | A Person | Kanban | 01/06/14 | Text1 |
3 | A Person | Stores | 02/06/14 | Text2 |
4 | 02/06/14 | Text3 | ||
5 | B Good | Kanban | 02/06/14 | Text4 |
6 | A Person | Kanban | 03/06/14 | Text5 |
7 | C Clearly | Kanban | 03/06/14 | Text6 |
8 | B Good | Carousel | 04/06/14 | Text7 |
9 | C Clearly | Stores | 05/06/14 | Text8 |
<tbody>
</tbody>
(Note: The blank cells in A4 and B4 are deliberate)
I don't want users to have access to this main data table, but I do want different users to be able to select some search criteria, then view a summary of their data. I've created a summary table on Sheet 2...
A | B | C | |
1 | T/L | (Dropdown list of names) | |
2 | Date From | (date) | |
3 | Date To | (date) | |
4 | |||
5 | Location | Date | Description |
6 | |||
7 | |||
8 | |||
9 | |||
10 | |||
11 |
<tbody>
</tbody>
The search criteria are entered by the user in cells B1 to B3.
In A6 (then copied across and down) I have the formula...
{=IF(ROWS(A$6:A6)<=(SUMPRODUCT(--(Sheet1!$A$2:$A$9=$B$1),--(Sheet1!$C$2:$C$9>=$B$2),--(Sheet1!$C$2:$C$9<=($B$3+0.999)))),INDEX(Sheet1!B$2:B$9,SMALL(IF((Sheet1!$A$2:$A$9=$B$1)*(Sheet1!$C$2:$C$9>=$B$2)*(Sheet1!$C$2:$C$9<=($B$3+0.999)),ROW(Sheet1!B$2:B$9)-ROW(Sheet1!B$2)+1),ROWS(A$6:A6))),"")}
So, if B1 = A Person, B2 = 01/06/14 and B3 = 03/06/14 the results I get are...
A | B | C | |
5 | Location | Date | Description |
6 | Kanban | 01/06/14 | Text1 |
7 | Stores | 02/06/14 | Text2 |
8 | Kanban | 03/06/14 | Text5 |
9 |
<tbody>
</tbody>
Is there a way to sort these results by location? I've tried all basic ways to sort them but no joy. I have looked at other posts where Autofilter has been suggested, but my VBA knowledge is weak and I can't figure out how to change any of the suggestions to fit my tables.
Alternatively, I'd be happy if there was a VBA routine to automatically copy and paste the values into another table which could be filtered and sorted by the user, but again, I can't figure out how to adapt the examples I've seen of this approach.
Help me Mr Excel forum... you're my only hope!!!
Thanks