I need to link only certain cell...

[CNA48]

I am trying to create a shipping list from a long list of items. On a given row I have a column called "Descrition" and a column called "ship". On my shipping list I want to link to the "Descrition" column only if there is an "x" under the "ship" column. My formula is this
=IF(Sheet1!S9="X",Sheet1!E9,IF(Sheet1!S10="X",Sheet1!E10,IF(Sheet1!S11="X",Sheet1!E11,"")))

This is fine to go and find the first row that has an "x". My question is how do I format the next formula to go and start after it finds the first "x" and so forth. I would appreciate any ideas. thanks

[Max]

One way using non array formulas ..

In Sheet1, assume the list of descriptions is within E9:E100,
and the "x"'s would be marked within S9:S100.

Use an empty col to the right, say col T
Put in T9: =IF(S9="x",ROW(),"")
Copy down to T100
(cover the extent of data in col E)

Then in Sheet2, suppose the list of descriptions is to be extracted in B2
down

Put in B2:
=IF(ISERROR(SMALL(Sheet1!$T$9:$T$100,ROW(A1))),"",INDEX(Sheet1!$E$9:$E$100,M
ATCH(SMALL(Sheet1!$T$9:$T$100,ROW(A1)),Sheet1!$T$9:$T$100,0)))

Copy B2 down to B93
(cover the same range extent as in Sheet1)

Col B will extract the desired results, all neatly bunched at the top
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"CNA48" <[email protected]> wrote in
message news:[email protected]...
>
> I am trying to create a shipping list from a long list of items. On a
> given row I have a column called "Descrition" and a column called
> "ship". On my shipping list I want to link to the "Descrition" column
> only if there is an "x" under the "ship" column. My formula is this
>
=IF(Sheet1!S9="X",Sheet1!E9,IF(Sheet1!S10="X",Sheet1!E10,IF(Sheet1!S11="X",S
heet1!E11,"")))
>
> This is fine to go and find the first row that has an "x". My question
> is how do I format the next formula to go and start after it finds the
> first "x" and so forth. I would appreciate any ideas. thanks
>
>
> --
> CNA48
> ------------------------------------------------------------------------
> CNA48's Profile:
http://www.excelforum.com/member.php...o&userid=28686
> View this thread: http://www.excelforum.com/showthread...hreadid=484261
>