Return Specific Value with IF Statement

[aburnikel]

I am attempting to pull data from another workbook. Here is my thoug
process on what I would like the function to do. I don't know if it i
possible or if I have to use VBA or another method.

if('cell from worksheet'<>"Inactive",return value of that cell,go t
next cell)

Basically, I have a list of names I would like to return, however, som
cell values are "INACTIVE", I would like to skip that cell and move t
the next cell that has an actual name. I know I can use the I
statement and return all the values, then sort and then delete thos
that are inactive - I plan on using a macro regardless, I just didn'
know if there was a "NEXT" function or "SKIP" function that might sav
me some time from bringing in unnecessary information.

any tips appreciated!!

Thanks,
Angi

 

[Frank Kabel]

Hi
one way using worksheet formulas: enter the following
array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet1'!$A$1:$A$1000,SMALL(IF('sheet1'!
$A$1:$A$1000<>"Inactive",ROW('sheet1'!$A$1:$A$1000)),ROW
(1:1)))

and copy this down as far as needed

 

[JE McGimpsey]

Safer:

=INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<>"Inactive",
ROW(INDIRECT("1:1000"))),ROW(1:1)))

That way, if a row is inserted above row 1 in Sheet1, the formula will
still return the correct value.

 

[Peo Sjoblom]

Or if you want to make it even safer <g>

=INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$A$1:$A$1000<>"Inactive",ROW(INDI
RECT("1:1000"))),ROW()-ROW($H$1)+1))

where H1 is the cell that holds the formula itself
that way it will work if a row is inserted above row 1 in the sheet that
holds the formula

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)