Eliminating Blank Cells From Lists on different worksheets

[Tim]

Hi

On Chip Pearson’s web page http://www.cpearson.com/excel/noblanks.htm I
found this Array formula for Eliminating Blank Cells From Lists:

=IF(ROW()-ROW(NoBlanksRange)+3>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),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 doesn’t affect the result but I am also not quite sure
what the purpose of this number in the formula is.

 

[Bob Phillips]

Add the sheet name to the INDIRECT part

=IF(ROW()-ROW(NoBlanksRange)+3>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",
INDIRECT("'sheet_name'!"&ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),
ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+3),COLUMN(BlanksRange),4)
))

--

HTH

RP
(remove nothere from the email address if mailing direct)

Hi

On Chip Pearson's web page http://www.cpearson.com/excel/noblanks.htm I
found this Array formula for Eliminating Blank Cells From Lists:

=IF(ROW()-ROW(NoBlanksRange)+3>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",

 

[Tim]

Thank you Bob!
This did the job.


Tim

 

[Bob Phillips]

Good. I did not know about that code on Chip's site, so we both got
something ).

Bob