Cell change - effects table filter??

[natheplas]

Hi,

On sheet 2 I have a dropdown list of words. When i select the word/account on sheet 2, Id like the filter to change on sheet 1 which has the same accounts.

So the dropdown on sheet 2 is on cell A4, if i select 'Loud' then i want this to effect sheet 1's table column A filters A2:A100000, to filter on 'Loud'.

Not sure if Excel can do it? Link a cell to a tables filter? Or how best to go about this if I need a macro etc?

Hope someone can help.

 

[natheplas]

Can anyone let me know if this is possible please?

I have a data validation list and I want to try and set it up so that when I select cell from the drop down, this effects a filter on another worksheet.

Maybe there is a macro I can run if needs be please? The data validation dropdown is in cell =ORCO!A5 and the filter I need to clear and change is in cell =ORC!A1

Huge appreciation if someone could come back to me please.

 

[Eric W]

I originally hoped that by using Advanced Filtering, we could accomplish your goal. But it doesn't seem to be dynamic, i.e. I can create the filter criterion using ORCO!A5 and a formula, but the filter does not automatically regenerate. So it looks like you'll need some variant of VBA to get this to work.

But first let's define it a bit better. You only want to show the rows which have the value from ORCO!A5 in column A on sheet ORC? To dynamically change when the value in ORCO!A5 changes? Is there anything special about ORC!A1, or is that just the cell where the filter arrow appears? You have a range of A2:A100000, how much is used? Are there empty cells?

For now, I'll just assume the basics. Go to sheet ORCO. Right click on the sheet tab on the bottom and select View Code. In the window that opens, paste this:

Private Sub Worksheet_Change(ByVal target As Range)
    If Intersect(target, Range("A5")) Is Nothing Then Exit Sub
    Sheets("ORC").Range("$A$1:$A$100000").AutoFilter Field:=1, Criteria1:=Range("A5").Value
End Sub

Press Alt-Q to exit the VBA editor. If you want, you can put =ORCO!A5 in ORC!A1. Now whenever you change the Data Validation in ORCO, the list in ORC will filter. I'm not quite sure why you'd want this - you get the same effect from just using the filter arrow.

Incidentally, that's probably why you didn't get any respondents for the question, it seemed a little ill-defined. I actually saw this earlier, but passed at the time since I didn't have time for any back and forth. Hope this helps!