From: DevourU on
I would like to have sheet1 list all rows in sheet2 matching "TEXT" in column
sheet2,A. Big TIA!

-JS
From: Ashish Mathur on
Hi,

Could you give an example

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"DevourU" <DevourU(a)discussions.microsoft.com> wrote in message
news:58909FE8-DB65-4B37-B251-F907AC8D405C(a)microsoft.com...
> I would like to have sheet1 list all rows in sheet2 matching "TEXT" in
> column
> sheet2,A. Big TIA!
>
> -JS

From: DevourU on
I was about to do it.
Sheet1 is blank.
show only rows from sheet2 in sheet1 that have "TEXT" in column sheet2!A:A

Actually I will want to return a few columns from the actual row, but I hope
to do that myself once I can get past this. TIA!

"Ashish Mathur" wrote:

> Hi,
>
> Could you give an example
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "DevourU" <DevourU(a)discussions.microsoft.com> wrote in message
> news:58909FE8-DB65-4B37-B251-F907AC8D405C(a)microsoft.com...
> > I would like to have sheet1 list all rows in sheet2 matching "TEXT" in
> > column
> > sheet2,A. Big TIA!
> >
> > -JS
>
From: DevourU on
Maybe this may help, I know it's wrong, but get us on the same page.
sheet1A:A=If('Sheet2'!A:A="TEXT", 'Sheet2'!H:H, next row)

"Ashish Mathur" wrote:

> Hi,
>
> Could you give an example
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "DevourU" <DevourU(a)discussions.microsoft.com> wrote in message
> news:58909FE8-DB65-4B37-B251-F907AC8D405C(a)microsoft.com...
> > I would like to have sheet1 list all rows in sheet2 matching "TEXT" in
> > column
> > sheet2,A. Big TIA!
> >
> > -JS
>
From: Jacob Skaria on
To return all rows with "text" in colA with all columns..

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

Apply this formula in Sheet2 cell A1 and copy/drag down and to the right as
required.

=IF(COUNTIF(Sheet1!$A$1:$A$1000,"test")<ROW(A1),"",
INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$1000="test",
ROW($A$1:$A$1000)),ROW(A1))))

--
Jacob (MVP - Excel)


"DevourU" wrote:

> I would like to have sheet1 list all rows in sheet2 matching "TEXT" in column
> sheet2,A. Big TIA!
>
> -JS