Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter the results of a list based on a previous vlookup against the same list | Excel Worksheet Functions | |||
How to identify text from a autofiltered list using formulas | Excel Worksheet Functions | |||
copy avg results | Excel Discussion (Misc queries) | |||
Copying to an autofiltered list | Excel Discussion (Misc queries) | |||
How to plot only visible autofiltered rows in a data list | Charts and Charting in Excel |