I need to link only certain cell...
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 |
I need to link only certain cell...
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 |
All times are GMT +1. The time now is 01:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com