Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Copy Results from Autofiltered List

Hi,

I use Autofilter on a list of 5 columns to narrow down a search. This
search will always be norrowed down to display only 1 record.

How can I copy that 1 record to other cells elswhere on the same page.

That is - the 1 record displayed from my Autofiltered list is
displayed from A10:E10, and I want this data copied to A5:E5.

Obviously I can't pull the contents of the top row of the filtered
list with =A5, =B5, =C5, =D5 as this will always change.

Tks, Kaye

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Copy Results from Autofiltered List

Perhaps one way which might achieve it ..

Illustrated in this sample construct:
http://www.savefile.com/files/650331
Copy Results from Autofiltered List.xls

Source table assumed in A9:E200, col headers in A9:E9

In A3:
=showfilter(A9:A200)
Note: Above requires Tom Ogilvy's ShowFilter UDF to be pre-installed.
Tom's UDF & steps to install are given below the sign-off

In A4:
=IF(ISNUMBER(SUBSTITUTE(A3,"=","")+0),SUBSTITUTE(A 3,"=","")+0,SUBSTITUTE(A3,"=",""))

Array-entered** in A5:
=IF(ISNA(MATCH(1,($A$10:$A$200=$A$4)*($B$10:$B$200 =$B$4)*($C$10:$C$200=$C$4)*($D$10:$D$200=$D$4)*($E $10:$E$200=$E$4),0)),"",INDEX(A10:A200,MATCH(1,($A $10:$A$200=$A$4)*($B$10:$B$200=$B$4)*($C$10:$C$200 =$C$4)*($D$10:$D$200=$D$4)*($E$10:$E$200=$E$4),0)) )

**Press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER

Select A3:A5, copy across to E5.
A5:E5 will return the required results from the top row in the filter.

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Tom Ogilvy's UDF

'---- begin vba -----
Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet

Application.Volatile

Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function
'-- end vba --

To implement the UDF:
Press Alt+F11 to go to VBE
Click Insert Module
Copy and paste the UDF (below) into the white space on the right [everything
within the dotted lines ("begin vba" to "end vba")]

Press Alt+Q to get back to Excel
Then in Excel, we can use Tom's UDF as, eg in A3: =showfilter(A9:A200)

"Kaye" wrote:
I use Autofilter on a list of 5 columns to narrow down a search. This
search will always be norrowed down to display only 1 record.

How can I copy that 1 record to other cells elswhere on the same page.

That is - the 1 record displayed from my Autofiltered list is
displayed from A10:E10, and I want this data copied to A5:E5.

Obviously I can't pull the contents of the top row of the filtered
list with =A5, =B5, =C5, =D5 as this will always change.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Copy Results from Autofiltered List


Thanks so much Max, that's exactly what I was after.

Regards, Kaye

On Wed, 18 Apr 2007 04:32:00 -0700, Max wrote:

Perhaps one way which might achieve it ..

Illustrated in this sample construct:
http://www.savefile.com/files/650331
Copy Results from Autofiltered List.xls

Source table assumed in A9:E200, col headers in A9:E9

In A3:
=showfilter(A9:A200)
Note: Above requires Tom Ogilvy's ShowFilter UDF to be pre-installed.
Tom's UDF & steps to install are given below the sign-off

In A4:
=IF(ISNUMBER(SUBSTITUTE(A3,"=","")+0),SUBSTITUTE( A3,"=","")+0,SUBSTITUTE(A3,"=",""))

Array-entered** in A5:
=IF(ISNA(MATCH(1,($A$10:$A$200=$A$4)*($B$10:$B$20 0=$B$4)*($C$10:$C$200=$C$4)*($D$10:$D$200=$D$4)*($ E$10:$E$200=$E$4),0)),"",INDEX(A10:A200,MATCH(1,($ A$10:$A$200=$A$4)*($B$10:$B$200=$B$4)*($C$10:$C$20 0=$C$4)*($D$10:$D$200=$D$4)*($E$10:$E$200=$E$4),0) ))

**Press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER

Select A3:A5, copy across to E5.
A5:E5 will return the required results from the top row in the filter.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Copy Results from Autofiltered List

Good to hear that.
You're welcome, Kaye.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kaye" wrote in message
...
Thanks so much Max, that's exactly what I was after.
Regards, Kaye



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter the results of a list based on a previous vlookup against the same list Mizpah Excel Worksheet Functions 2 August 18th 06 10:28 AM
How to identify text from a autofiltered list using formulas Harryac Excel Worksheet Functions 1 July 13th 06 11:45 PM
copy avg results worduser Excel Discussion (Misc queries) 1 April 28th 06 08:14 PM
Copying to an autofiltered list Phil Excel Discussion (Misc queries) 3 June 30th 05 02:08 PM
How to plot only visible autofiltered rows in a data list Craig Charts and Charting in Excel 1 June 28th 05 08:38 PM


All times are GMT +1. The time now is 09:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"