link columns in different worksheets but removing blank cells

Hi everyone,

I have text data in a column in a worksheet (which can sometimes be more than 255 characters). I want anything that shows up in this column to show up in another column in another worksheet within the same workbook, BUT I would like for all blank cells to be removed when it goes over to the other column. Any ideas?

Thank you very much.

Hello fbarbie:

Assuming your list is in Sheet1 column A.

Paste this formula in Sheet2 cell A1 and copy down.

=IF(Sheet1!A1="","",ROW(1:1))

Paste this formula in Sheet2 cell B1 and copy down.

=IF(ISERR(SMALL(A:A,ROW(1:1))),"",OFFSET(Sheet1!A$1,SMALL(A:A,ROW(1:1))-1,0))


Matt

...wicked...

Thank you very much. That did the trick exactly.