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",Shee t1!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


--
CNA48
------------------------------------------------------------------------
CNA48's Profile: http://www.excelforum.com/member.php...o&userid=28686
View this thread: http://www.excelforum.com/showthread...hreadid=484261

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" wrote in
message ...

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",Shee t1!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