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

--
Message posted from http://www.ExcelForum.com

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

>-----Original Message-----
>I am attempting to pull data from another workbook. Here
is my though
>process on what I would like the function to do. I don't
know if it is
>possible or if I have to use VBA or another method.
>
>if('cell from worksheet'<>"Inactive",return value of that
cell,go to
>next cell)
>
>Basically, I have a list of names I would like to return,
however, some
>cell values are "INACTIVE", I would like to skip that
cell and move to
>the next cell that has an actual name. I know I can use
the IF
>statement and return all the values, then sort and then
delete those
>that are inactive - I plan on using a macro regardless, I
just didn't
>know if there was a "NEXT" function or "SKIP" function
that might save
>me some time from bringing in unnecessary information.
>
>any tips appreciated!!
>
>Thanks,
>Angie
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>

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.


In article <2a61f01c465b7$d466bd60$(E-Mail Removed)>,
"Frank Kabel" <(E-Mail Removed)> wrote:

> 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)))

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)



"JE McGimpsey" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> 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.
>
>
> In article <2a61f01c465b7$d466bd60$(E-Mail Removed)>,
> "Frank Kabel" <(E-Mail Removed)> wrote:
>
> > 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)))