Getting all rows of data that have a value in a particular column

[Biff]

Not sure I understand what you mean?

Biff

Sorry it took so long . . . insane week.
This works exactly like I want it to on the lines I got from your example.
I can't seem to make the copy to other lines work. Is there a trick?

 

[Guest]

Normally I just drag down to other cells. When I do that with this formula,
I get #REF in the destination cells. I even tried copy and paste. What
other way is there to get a perfect formula into subsequent rows?

sunny

 

[Biff]

Can you send me a copy of your file? I don't need the whole thing if it's a
huge file, just the sheet where you're getting these errors. The only thing
that I can think of that would cause #REF! errors is if you were somehow
returning references that were outside of the range in the INDEX portion of
the formula. If you can send me the file I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

 

[Guest]

Hi,

I am trying to do something similar. I want to call values from another
worksheet but ignore the values that are "n/a". The link you posted does not
have the formula anymore.

Thanks for your time, UT

 

[T. Valko]

Can you provide some more specific details?

Is "n/a" a TEXT entry or is it the Excel "error" value #N/A ?

Biff

 

[Guest]

Hi,

The "n/a" values are valid text entry. However in another worksheet I want
to ignore the "n/a" text and only link to other cell values in that column. I
have read about the formatting the not required values in white font in the
conditional formatting. But that does not serve my purpose. I only want to
link to the text values other than "n/a". Is there a way to do this? Thanks.

 

[T. Valko]

Let's assume you have this data in A1:A5 -

n/a
blue
n/a
n/a
red

You want to extract all entries that are not n/a to another location.

If there are no empty/blank cells as above try this array** formula:

=IF(ROWS($1:1)<=COUNTIF(rng,"<>n/a"),INDEX(rng,SMALL(IF(rng<>"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

If there might be empty/blank cells try this array** formula:

=IF(ROWS($1:1)<=SUMPRODUCT(--(rng<>""),--(rng<>"n/a")),INDEX(rng,SMALL(IF((rng<>"n/a")*(rng<>""),ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

Copy down until you get blanks.

** array formulas *MUST* be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[Guest]

Hi Biff:

I tried the following formula but it didn't work. It give "name" error. What
am I doing wrong? Thanks

=IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng,"<>n/a"),INDEX(rng,SMALL(IF(rng<>"n/a",ROW(rng)-MIN(ROW(rng))+1),ROWS(GISData!H$8:H$500))),"")

 

[JMay]

In the formula where you see >>> rng << This is referring to a named
Range, which in your case is Range(A1:A5). Select the Range A1:A5 (and
while selected (In the NamedRange Box type in "rng" (without the
quotes),
thereby creating a named range equal to Range A1:A5) then the formulas
will work,,,

HTH

 

[Guest]

Thanks for the tip. I had never used range before.

I named my column range - "rng1" (without quotes), in the other worksheet.
In this worksheet I entered the range name in the following formula. There
are blank cells and cells with "n/a" value that I want to ignore.

The formula still doesn't work though. The cell remains blank after I
entered this formula.

=IF(ROWS(GISData!H$8:H$500)<=COUNTIF(rng1,"<>n/a"),INDEX(rng1,SMALL(IF(rng1<>"n/a",ROW(rng1)-MIN(ROW(rng1))+1),ROWS(GISData!H$8:H$500))),"")

Thanks for your help.

 

[T. Valko]

Change each instance of:

ROWS(GISData!H$8:H$500)

To:

ROWS($1:1)

ROWS($1:1) is just a "counter" and has nothing to do with the actual range
GISData!H$8:H$500

Biff

 

[Guest]

The formula worked perfectly. Thanks a ton.

One more question, my worksheet is linked to sql server database. Everytime
I refresh data on the worksheet the column width is adjusted to the column
heading size. I have tried to specify the column width but it changes upon
refreshing the data. Is there a way to lock the column width or row height.

Thanks.

 

[Tanya]

I have exactly the same question. I have the list for inventory and if the
item number is lower than reorder level, then there will be a "warning"
written at the beginning of the row automatically. Then I want to copy those
rows with "warning" to another sheet, then I can generate my reorder list. So
how can I do?? Thank you!