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.

Add the sheet name to the INDIRECT part

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

--

HTH

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


"Tim" wrote in message
...
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)+3ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",
INDIRECT(ADDRESS(SMALL((IF(BlanksRange<"",ROW(Bla nksRange),ROW()+ROWS(Blank
sRange))),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.

Thank you Bob!
This did the job.


Tim

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

Bob


"Tim" wrote in message
...
Thank you Bob!
This did the job.


Tim