I am trying to make a spread sheet so if for example on sheet 1 I have

A B C D
1 Big $5 3
2 Medium $3 5 yes
3 Small $1 4

I have a "yes" for the Medium row, How do I make it so that only the medim
row shows up on sheet 2 but if the other 2 would have "yes" in the D column
they would too?

something like this should work:

=IF(ISERROR(INDEX('Sheet1'!A:A,SMALL(IF('Sheet1'!$ D$1:$D$10="yes",ROW('Sheet1'!$D$1:$D$10)),ROW()))) ,"",INDEX('Sheet1'!A:A,SMALL(IF('Sheet1'!$D$1:$D$1 0="yes",ROW('Sheet1'!$D$1:$D$10)),ROW())))


Note that the reference to column D can't callout the entire column (might
be able to in 2007, I can't confirm). increase range callout as needed. Note
this formula should be placed in desired start of column A. Copy down and
across as needed.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Muskrat24" wrote:

I am trying to make a spread sheet so if for example on sheet 1 I have

A B C D
1 Big $5 3
2 Medium $3 5 yes
3 Small $1 4

I have a "yes" for the Medium row, How do I make it so that only the medim
row shows up on sheet 2 but if the other 2 would have "yes" in the D column
they would too?

it came up as error, there are not the same number of grouping brackets
"Luke M" wrote:

something like this should work:

=IF(ISERROR(INDEX('Sheet1'!A:A,SMALL(IF('Sheet1'!$ D$1:$D$10="yes",ROW('Sheet1'!$D$1:$D$10)),ROW()))) ,"",INDEX('Sheet1'!A:A,SMALL(IF('Sheet1'!$D$1:$D$1 0="yes",ROW('Sheet1'!$D$1:$D$10)),ROW())))


Note that the reference to column D can't callout the entire column (might
be able to in 2007, I can't confirm). increase range callout as needed. Note
this formula should be placed in desired start of column A. Copy down and
across as needed.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Muskrat24" wrote:

I am trying to make a spread sheet so if for example on sheet 1 I have

A B C D
1 Big $5 3
2 Medium $3 5 yes
3 Small $1 4

I have a "yes" for the Medium row, How do I make it so that only the medim
row shows up on sheet 2 but if the other 2 would have "yes" in the D column
they would too?

Hi,

I have something similar in question 7 of the following link -
http://ashishmathur.com/knowledgebaseII.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Muskrat24" wrote in message
...
I am trying to make a spread sheet so if for example on sheet 1 I have

A B C D
1 Big $5 3
2 Medium $3 5 yes
3 Small $1 4

I have a "yes" for the Medium row, How do I make it so that only the medim
row shows up on sheet 2 but if the other 2 would have "yes" in the D
column
they would too?