Naming a non-blank range from a col containing non-consec. blank c
 

Excel 2007. I have a column of data containing several blank cells. These
blank cells are spread out all through the column. I want to define a named
range that contains only the non-blank cells of this column.

I have seen a previous post of how to do this using an array formula, but
the solution not only looks cumbersome but doesn't quite appear to do what
I'm looking for anyway. This solution was found at
cpearson.com/excel/noblanks.htm

Does anyone know of a cleaner solution than this?

Naming a non-blank range from a col containing non-consec. blank c
 

An alternative play using non-array formulas ..

Assume the data (with interspersed blanks) is in Sheet1, in A1 down

In B1:
=IF(A1="","",ROW())

In C1:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(A:A,SMALL(B:B,R OWS($1:1))))
Select B1:C1, copy down to cover the max expected extent of data in col A,
eg down to C200. Col C returns what's in col A but wo blanks.

Then just define a dynamic named range, eg: MyRange
to point to col C, via: Insert Name Define with settings:

Names in workbook:
MyRange

Refers to:
=OFFSET(Sheet1!$C:$C,,,SUMPRODUCT(--(Sheet1!$C$1:$C$200<"")))

(Adapt the range $C$1:$C$200 to suit the max expected extent)

Above works in xl2003 (my ver). I don't know/have xl2007, but think it
should work just as well <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"fruitticher" wrote:
Excel 2007. I have a column of data containing several blank cells. These
blank cells are spread out all through the column. I want to define a named
range that contains only the non-blank cells of this column.

I have seen a previous post of how to do this using an array formula, but
the solution not only looks cumbersome but doesn't quite appear to do what
I'm looking for anyway. This solution was found at
cpearson.com/excel/noblanks.htm

Does anyone know of a cleaner solution than this?

Naming a non-blank range from a col containing non-consec. bla
 

Max,

Brilliant! Thanks a ton.

- fruitticher



"Max" wrote:

An alternative play using non-array formulas ..

Assume the data (with interspersed blanks) is in Sheet1, in A1 down

In B1:
=IF(A1="","",ROW())

In C1:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(A:A,SMALL(B:B,R OWS($1:1))))
Select B1:C1, copy down to cover the max expected extent of data in col A,
eg down to C200. Col C returns what's in col A but wo blanks.

Then just define a dynamic named range, eg: MyRange
to point to col C, via: Insert Name Define with settings:

Names in workbook:
MyRange

Refers to:
=OFFSET(Sheet1!$C:$C,,,SUMPRODUCT(--(Sheet1!$C$1:$C$200<"")))

(Adapt the range $C$1:$C$200 to suit the max expected extent)

Above works in xl2003 (my ver). I don't know/have xl2007, but think it
should work just as well <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"fruitticher" wrote:
Excel 2007. I have a column of data containing several blank cells. These
blank cells are spread out all through the column. I want to define a named
range that contains only the non-blank cells of this column.

I have seen a previous post of how to do this using an array formula, but
the solution not only looks cumbersome but doesn't quite appear to do what
I'm looking for anyway. This solution was found at
cpearson.com/excel/noblanks.htm

Does anyone know of a cleaner solution than this?

Naming a non-blank range from a col containing non-consec. bla
 

welcome. good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"fruitticher" wrote in message
...
Max,

Brilliant! Thanks a ton.

- fruitticher