A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Filter and display rows on another worksheet



 
 
Thread Tools Display Modes
  #1  
Old June 11th, 2004, 08:39 PM
JML
external usenet poster
 
Posts: n/a
Default Filter and display rows on another worksheet

Thanks Frank,

Is there a way to automate the "Advanced Filter" to work like a function? I want to be able to enter the criteria in a cell and have the filter automatically calculate all of the time.

Thanks

"Frank Kabel" wrote:

Hi
sorry, my fault. Try using 'Data - Filter - Advanced Filter for this or
have a look at:
http://www.rondebruin.nl/copy5.htm

--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Thanks Frank,

The issue is that I don't want to summarize any of the data, but the
data area of the pivot table forces me to summarize (count, add,
etc.) the data. I want all of the actual values for the criteria I
specify to appear.

Thanks

"Frank Kabel" wrote:

Hi
use a pivot table for this. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


wrote:
Hello,

I would like help filtering a range of data and displaying it on
another worksheet based on criteria listed in the 2nd worksheet.

Ex. (Worksheet #1)
Column(A) Column(B)
Row(1) Job# amount
Row(2) 1001 1
Row(3) 1002 2
Row(4) 1002 3
Row(5) 1001 4

(Worksheet #2) - This is the result I am looking for.

Column(A) Column(B)
Row(1) Criteria= 1001
Row(2) job# amount
Row(3) 1001 1
Row(4) 1001 4

Thanks in advance for the help!!

Josh



  #2  
Old June 11th, 2004, 08:54 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default Filter and display rows on another worksheet

Hi
if you want to do this automatically you can do this with some array
formulas. e.g. try the following formulas on sheet2:
A3: a non array formula
=IF(B3"",$B$1,"")

B3: enter the array formula (entered with CTRL+SHIFT+ENTER)
=IF(ISERROR(INDEX('sheet1'!$B$1:$B$100,SMALL(IF('s heet1'!$A$2:$A$100=$B
$1,ROW('sheet1'!$A$2:$A$100)),ROW(1:1)))),"",INDEX ('sheet1'!$B$1:$B$100
,SMALL(IF('sheet1'!$A$2:$A$100=$B$1,ROW('sheet1'!$ A$2:$A$100)),ROW(1:1)
)))

copy both formulas down as far as needed


--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Thanks Frank,

Is there a way to automate the "Advanced Filter" to work like a
function? I want to be able to enter the criteria in a cell and have
the filter automatically calculate all of the time.

Thanks

"Frank Kabel" wrote:

Hi
sorry, my fault. Try using 'Data - Filter - Advanced Filter for this
or have a look at:
http://www.rondebruin.nl/copy5.htm

--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:
Thanks Frank,

The issue is that I don't want to summarize any of the data, but

the
data area of the pivot table forces me to summarize (count, add,
etc.) the data. I want all of the actual values for the criteria I
specify to appear.

Thanks

"Frank Kabel" wrote:

Hi
use a pivot table for this. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


wrote:
Hello,

I would like help filtering a range of data and displaying it on
another worksheet based on criteria listed in the 2nd worksheet.

Ex. (Worksheet #1)
Column(A) Column(B)
Row(1) Job# amount
Row(2) 1001 1
Row(3) 1002 2
Row(4) 1002 3
Row(5) 1001 4

(Worksheet #2) - This is the result I am looking for.

Column(A) Column(B)
Row(1) Criteria= 1001
Row(2) job# amount
Row(3) 1001 1
Row(4) 1001 4

Thanks in advance for the help!!

Josh


  #3  
Old June 11th, 2004, 09:34 PM
icestationzbra
external usenet poster
 
Posts: n/a
Default Filter and display rows on another worksheet

i have this piece of code that i got from a file that i downloaded from
a website (lacher).

its on a sheet which has 4 columns. there are 2 input parameters which
define the filtering. it could be tweaked around a little bit and used
for your purpose. i am just too lazy to try it for your example right
now. let me know if help is needed with tweaking.

mac.
***

Sub Filter_Test()

'\ update criteria values in hidden worksheet
With Worksheets("CriteriaValues").Range("Database")
.Cells(2, 1).Value = InputBox(prompt:="Enter Year for Filter")
.Cells(2, 2).Value = InputBox(prompt:="Enter Month for
Filter")
End With

'\ use hidden criteria range to filter data on datavalues
worksheet
Worksheets("DataValues").Range("Database").Advance dFilter
Action:=xlFilterInPlace, CriteriaRange _
:=Worksheets("CriteriaValues").Range("Database"),
Unique:=False
End Sub


---
Message posted from http://www.ExcelForum.com/

  #4  
Old June 11th, 2004, 10:33 PM
Alan Beban
external usenet poster
 
Posts: n/a
Default Filter and display rows on another worksheet

The function I mentioned in the other thread you started on this same
topic can be used that way.

Alan Beban

JML wrote:

Thanks Frank,

Is there a way to automate the "Advanced Filter" to work like a function? I want to be able to enter the criteria in a cell and have the filter automatically calculate all of the time.

Thanks

"Frank Kabel" wrote:


Hi
sorry, my fault. Try using 'Data - Filter - Advanced Filter for this or
have a look at:
http://www.rondebruin.nl/copy5.htm

--
Regards
Frank Kabel
Frankfurt, Germany


JML wrote:

Thanks Frank,

The issue is that I don't want to summarize any of the data, but the
data area of the pivot table forces me to summarize (count, add,
etc.) the data. I want all of the actual values for the criteria I
specify to appear.

Thanks

"Frank Kabel" wrote:


Hi
use a pivot table for this. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


wrote:

Hello,

I would like help filtering a range of data and displaying it on
another worksheet based on criteria listed in the 2nd worksheet.

Ex. (Worksheet #1)
Column(A) Column(B)
Row(1) Job# amount
Row(2) 1001 1
Row(3) 1002 2
Row(4) 1002 3
Row(5) 1001 4

(Worksheet #2) - This is the result I am looking for.

Column(A) Column(B)
Row(1) Criteria= 1001
Row(2) job# amount
Row(3) 1001 1
Row(4) 1001 4

Thanks in advance for the help!!

Josh



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


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


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