Hi
On Chip Pearsons web page
http://www.cpearson.com/excel/noblanks.htm I
found this Array formula for Eliminating Blank Cells From Lists:
=IF(ROW()-ROW(NoBlanksRange)+3ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL( (IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),ROW()-ROW(NoBlanksRange)+3),COLUMN(BlanksRange),4)))
The formula works great when BlanksRange and NoBlanksRange are on the same
worksheet. Unfortunately my BlanksRange and NoBlanksRange are on different
worksheets. Both columns have same number of rows and start in the same row
3. In this case the formula gives me values of 0 for the cells in the
NoBlanksRange.
Does anybody know how to correct this formula to work when the BlanksRange
and NoBlanksRange are on different worksheets? In my case number 4 at the
end of the formula doesnt affect the result but I am also not quite sure
what the purpose of this number in the formula is.