On 2002-10-05 11:38, woodzilla wrote:

After much fooling around, I figured out how to use the RETURNING NONBLANK CELLS FROM A RANGE, from page 354 of the Excel 2000 Formulas book. So for all other readers of this message, here's what I did.

The problem, I have dozens of columns of data, each with 100s of rows of numbers, over half that are blank cells. I wanted to summarize the columns of numbers so there is no blanks, and updates dynamically.

Select each column of source data cells,name each one,(from menu, select insert-name-define).

Copy or type in the original formula into wordpad. Copy that as many times as you have named source data columns. Then you need to go into each formula and replace the word "data" with the name of your own named data column, in 7 different places within the formula.

In Excel, select a new destination column, in my case, 300 rows,(can be on a different sheet) copy and paste the modified formula from wordpad into the formula bar, not a cell. Hit shift+ctrl+enter to send the new formula into the new array.

The new list of numbers without blanks, should pop up in a few seconds.