Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell link from the filtered data
hello there,
I have this data: column A xxxx xxxx zzzz zzzz zzzz xxxx yyyy when I filtered it & choose the xxxx, the data shows this: xxxx xxxx xxxx Now, my problem is, i want to make a formula which can give a result from the filtered data. In this case the result should be xxxx. can somebody help me with this. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell link from the filtered data
Here's a nice Function **written by Stephen Bullen** that has helped me
overthe years. Put in a Standard module and in your spreadsheet enter: =FilterCriteria(AFtableRange) HTH Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function "EricBB" wrote: hello there, I have this data: column A xxxx xxxx zzzz zzzz zzzz xxxx yyyy when I filtered it & choose the xxxx, the data shows this: xxxx xxxx xxxx Now, my problem is, i want to make a formula which can give a result from the filtered data. In this case the result should be xxxx. can somebody help me with this. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell link from the filtered data
Now I got what I really need.
Many Thanks Jim. "Jim May" wrote: Here's a nice Function **written by Stephen Bullen** that has helped me overthe years. Put in a Standard module and in your spreadsheet enter: =FilterCriteria(AFtableRange) HTH Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " AND " & .Criteria Case xlOr Filter = Filter & " OR " & .Criteria End Select End With End With Finish: FilterCriteria = Filter End Function "EricBB" wrote: hello there, I have this data: column A xxxx xxxx zzzz zzzz zzzz xxxx yyyy when I filtered it & choose the xxxx, the data shows this: xxxx xxxx xxxx Now, my problem is, i want to make a formula which can give a result from the filtered data. In this case the result should be xxxx. can somebody help me with this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How 2 Link DATA instead of CELL REFERENCE - Pulling My Hair Out! | New Users to Excel | |||
how do i copy data from the top cell of a filtered list to anothe. | Excel Worksheet Functions | |||
Use filename in cell to link data | Excel Worksheet Functions | |||
link dynamic data to cell? | Excel Discussion (Misc queries) | |||
How do I link data from a cell on one spread sheet to another? | Excel Worksheet Functions |