Eliminating Blank Cells From Lists on different worksheets

T

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

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)


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:
=IF(ROW()-ROW(NoBlanksRange)+3>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",
 
B

Bob Phillips

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

Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top