Eliminating Blank Cells From Lists on different worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)


Tim said:
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:
Click to expand...
=IF(ROW()-ROW(NoBlanksRange)+3>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",
 
Good. I did not know about that code on Chip's site, so we both got
something :-)).

Bob
 
Back
Top