software

Hello..
I need a formula to work like the ‘Filter’ function does.

Text Example : (page1)
ABCD
1John10:3010/12/05Yes
2Jack11:0005/02/06Yes
3John14:1030/12/05Pass
4John18:0015/03/04No
5Lea15:3411/02/05Pass
And so on …

I require a formula on page2 to list all the attributes of a specified name
in column ‘A of page1’ similar to what a filter can do.
The name is liable to change (not fixed), so the formula will need to
perform a lookup of cell ‘H1’ for what name to return (in example the name in
‘H1’ is ‘John’)

Formula Return Example : (page2)
ABCDH
1John10:3010/12/05YesJohn
2John14:1030/12/05Pass
3John18:0015/03/04No

The formula can’t produce any blank rows or duplicate lines where the
incorrect name maybe (this is the fault of my existing formula),

Nor can I simply get away with using the filter function (unfortunately)
unless someone knows how to return the name specified by the filter in cell
H1 (ie. I manually choose to filter ‘John’ and cell ‘H1’ detects this and
shows ‘John’)

Many thanks for any advice..
Monk.Hi Monk,

Have you tried using a pivot table ,

Not sure if that will work fo r you - depneds if you want to count or total
anything...

wAyne_

quot;Monkquot; wrote:

gt; Hello..
gt; I need a formula to work like the ‘Filter’ function does.
gt;
gt; Text Example : (page1)
gt; ABCD
gt; 1John10:3010/12/05Yes
gt; 2Jack11:0005/02/06Yes
gt; 3John14:1030/12/05Pass
gt; 4John18:0015/03/04No
gt; 5Lea15:3411/02/05Pass
gt; And so on …
gt;
gt; I require a formula on page2 to list all the attributes of a specified name
gt; in column ‘A of page1’ similar to what a filter can do.
gt; The name is liable to change (not fixed), so the formula will need to
gt; perform a lookup of cell ‘H1’ for what name to return (in example the name in
gt; ‘H1’ is ‘John’)
gt;
gt; Formula Return Example : (page2)
gt; ABCDH
gt; 1John10:3010/12/05YesJohn
gt; 2John14:1030/12/05Pass
gt; 3John18:0015/03/04No
gt;
gt; The formula can’t produce any blank rows or duplicate lines where the
gt; incorrect name maybe (this is the fault of my existing formula),
gt;
gt; Nor can I simply get away with using the filter function (unfortunately)
gt; unless someone knows how to return the name specified by the filter in cell
gt; H1 (ie. I manually choose to filter ‘John’ and cell ‘H1’ detects this and
gt; shows ‘John’)
gt;
gt; Many thanks for any advice..
gt; Monk.
gt;

Not sure how the name gets into H1 but provided it is there and you
want all rows on page 1 with that name you could still use the
autofilter if you do this:

In column E on page one type this in E1: =if(a1=page2!$H$1;1;quot;quot;) and
copy down as required.

Then turn on the autofilter for column E and select quot;1quot; from the list

Hope I got your problem.

HansIf a pivot table wont do for you -- here is something that will work..

you will need to add a couple of coulmns to your data sheet so it will look
like this..
ABCDEFG
1John11John10:3010-Dec-05yes
1Jack12Jack11:005-Feb-06yes
2John23John14:1030-Dec-05pass
3John34John18:0015-Mar-04no
1Lea15Lea15:3411-Feb-05pass

1. Column A you can set using =COUNTIF(D$1,D2,D2)
if you use this and fill down for each row - it will give you the number for
each opccurence.
2 Column B is =CONCATENATE(D2. A2)

Then on Sheet 2 you can place

John23456
11John10:3012/10yes
23John14:1012/30pass
34John18:003/15no
4
5
6
7

Cell A1 is your Filter
Cell A2 .... is a count of lines

Cell A2 - A.... is the column number for your list just makes it easier to
copy the vlookups...

Cell B2 ..... is your vlookup as follows
=IF(ISERROR(VLOOKUP(CONCATENATE($A$1,$A2),Sheet1!$ B:$G,B$1,FALSE)),quot;quot;,VLOOKUP(CONCATENATE($A$1,$A2), Sheet1!$B:$G,B$1,FALSE))
just place this in B3 then fill down and right..Hope you can make sens out of it...

wAyne_
quot;wAynequot; wrote:

gt; Hi Monk,
gt;
gt; Have you tried using a pivot table ,
gt;
gt; Not sure if that will work fo r you - depneds if you want to count or total
gt; anything...
gt;
gt; wAyne_
gt;
gt; quot;Monkquot; wrote:
gt;
gt; gt; Hello..
gt; gt; I need a formula to work like the ‘Filter’ function does.
gt; gt;
gt; gt; Text Example : (page1)
gt; gt; ABCD
gt; gt; 1John10:3010/12/05Yes
gt; gt; 2Jack11:0005/02/06Yes
gt; gt; 3John14:1030/12/05Pass
gt; gt; 4John18:0015/03/04No
gt; gt; 5Lea15:3411/02/05Pass
gt; gt; And so on …
gt; gt;
gt; gt; I require a formula on page2 to list all the attributes of a specified name
gt; gt; in column ‘A of page1’ similar to what a filter can do.
gt; gt; The name is liable to change (not fixed), so the formula will need to
gt; gt; perform a lookup of cell ‘H1’ for what name to return (in example the name in
gt; gt; ‘H1’ is ‘John’)
gt; gt;
gt; gt; Formula Return Example : (page2)
gt; gt; ABCDH
gt; gt; 1John10:3010/12/05YesJohn
gt; gt; 2John14:1030/12/05Pass
gt; gt; 3John18:0015/03/04No
gt; gt;
gt; gt; The formula can’t produce any blank rows or duplicate lines where the
gt; gt; incorrect name maybe (this is the fault of my existing formula),
gt; gt;
gt; gt; Nor can I simply get away with using the filter function (unfortunately)
gt; gt; unless someone knows how to return the name specified by the filter in cell
gt; gt; H1 (ie. I manually choose to filter ‘John’ and cell ‘H1’ detects this and
gt; gt; shows ‘John’)
gt; gt;
gt; gt; Many thanks for any advice..
gt; gt; Monk.
gt; gt;

Thanks Guys,

Flummi,
Im preferring your method (as it described) as I have the H1 text entry
covered..

But I cant seem to get to cell to accept the formula (appears to have an
issue with the $H$1 part).. Am I missing a space or colon or something ..?

=if(a1=page2!$H$1;1;quot;quot;)

Thanks,
J
quot;flummiquot; wrote:

gt; Not sure how the name gets into H1 but provided it is there and you
gt; want all rows on page 1 with that name you could still use the
gt; autofilter if you do this:
gt;
gt; In column E on page one type this in E1: =if(a1=page2!$H$1;1;quot;quot;) and
gt; copy down as required.
gt;
gt; Then turn on the autofilter for column E and select quot;1quot; from the list
gt;
gt; Hope I got your problem.
gt;
gt; Hans
gt;
gt;

Might be a delimiter issue. Have you tried substituting the semicolons with
commas?

=if(a1=page2!$H$1,1,quot;quot;)

--
Kevin Vaughnquot;Monkquot; wrote:

gt; Thanks Guys,
gt;
gt; Flummi,
gt; Im preferring your method (as it described) as I have the H1 text entry
gt; covered..
gt;
gt; But I cant seem to get to cell to accept the formula (appears to have an
gt; issue with the $H$1 part).. Am I missing a space or colon or something ..?
gt;
gt; =if(a1=page2!$H$1;1;quot;quot;)
gt;
gt; Thanks,
gt; J
gt;
gt;
gt;
gt; quot;flummiquot; wrote:
gt;
gt; gt; Not sure how the name gets into H1 but provided it is there and you
gt; gt; want all rows on page 1 with that name you could still use the
gt; gt; autofilter if you do this:
gt; gt;
gt; gt; In column E on page one type this in E1: =if(a1=page2!$H$1;1;quot;quot;) and
gt; gt; copy down as required.
gt; gt;
gt; gt; Then turn on the autofilter for column E and select quot;1quot; from the list
gt; gt;
gt; gt; Hope I got your problem.
gt; gt;
gt; gt; Hans
gt; gt;
gt; gt;

Sorry, you are right.

I'm sitting in Germany and we commonly use semicolons as list
separators.

Lookup your local Windoes settings for quot;regional and language
settingsquot;. Like Kevin says it's probably a comma.

Good luck!

HansCool this seems to work Brill..
Thanks Guys for your help

Monk..
quot;Kevin Vaughnquot; wrote:

gt; Might be a delimiter issue. Have you tried substituting the semicolons with
gt; commas?
gt;
gt; =if(a1=page2!$H$1,1,quot;quot;)
gt;
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;Monkquot; wrote:
gt;
gt; gt; Thanks Guys,
gt; gt;
gt; gt; Flummi,
gt; gt; Im preferring your method (as it described) as I have the H1 text entry
gt; gt; covered..
gt; gt;
gt; gt; But I cant seem to get to cell to accept the formula (appears to have an
gt; gt; issue with the $H$1 part).. Am I missing a space or colon or something ..?
gt; gt;
gt; gt; =if(a1=page2!$H$1;1;quot;quot;)
gt; gt;
gt; gt; Thanks,
gt; gt; J
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;flummiquot; wrote:
gt; gt;
gt; gt; gt; Not sure how the name gets into H1 but provided it is there and you
gt; gt; gt; want all rows on page 1 with that name you could still use the
gt; gt; gt; autofilter if you do this:
gt; gt; gt;
gt; gt; gt; In column E on page one type this in E1: =if(a1=page2!$H$1;1;quot;quot;) and
gt; gt; gt; copy down as required.
gt; gt; gt;
gt; gt; gt; Then turn on the autofilter for column E and select quot;1quot; from the list
gt; gt; gt;
gt; gt; gt; Hope I got your problem.
gt; gt; gt;
gt; gt; gt; Hans
gt; gt; gt;
gt; gt; gt;

Hi,

I'm using something similar to this, thanks flummi, however when I update
the item in H1 (using the example below) form a validation list the
autofilter doesn't seem to update so I still see the results of the previous
autofilter.

eg. in H1 the original text is quot;ARTquot; then the next item is selected from the
list quot;CONquot;. Obviously the formula below updates the column containing the
formula below as the new selection is made. I was hoping that this
automatically update the autofilter but it does not. Is there anyway to force
the filter to refresh?

Thanks,
Andrew

quot;flummiquot; wrote:

gt; Not sure how the name gets into H1 but provided it is there and you
gt; want all rows on page 1 with that name you could still use the
gt; autofilter if you do this:
gt;
gt; In column E on page one type this in E1: =if(a1=page2!$H$1;1;quot;quot;) and
gt; copy down as required.
gt;
gt; Then turn on the autofilter for column E and select quot;1quot; from the list
gt;
gt; Hope I got your problem.
gt;
gt; Hans
gt;
gt;