Question about dynamic named range

[Bobocat]

Hi,

I learnt this from the previous post. However, do you have any idea to
create the dynamic named range that will cover some blank cells?

Thank you in advance.

Bobocat

 

[Biff]

Hi,

I learnt this from the previous post. However, do you have any idea to
create the dynamic named range that will cover some blank cells?

What previous post are you referring to?

If you have empty cells *within* the range then you have to identify the
last filled cell:

http://xldynamic.com/source/xld.LastValue.html

Biff

 

[Arvi Laanemets]

Hi

It depends on your data. You need some formula, which locates the last row
of used range. Some possibilities:

a) You use a helping column as key. The column contains a formula, which
counts rows in used range. P.e. column B contains some data, with header in
B1. Into A2 you enter the formula
=IF(OR(B2<>"",A3<>""),ROW()-1,"")
, and copy it down. Now you can define a named range for column B as
=OFFSET(Sheet1!$B$1,1,,MAX(Sheet1!$A:$A),1)

b) You create an UDF, which returns the number of rows/columns in used
range, and then use this UDF to define a named range.

c) You use some built-in formula to calculate the last row in used range.
P.e. when your data in column A are sorted ascending
=OFFSET(Sheet1!$A$1,1,,CHOOSE(MAX(Sheet1!$A$2:$A$1000),Sheet1!$A$2:$A$1000,0
),1)

Arvi Laanemets

 

[Don Guillett]

where you are looking for a larger than possible number or letter.
=OFFSET(Sheet1!$A$1,0,0,MAX(MATCH(99999,Sheet1!$A:$A),MATCH("zzzz",Sheet1!$A:$A)),1)

 

[Bobocat]

I tried to copy the following formula to the defination of the name range,
it returns "reference is not valid"

 

[Bobocat]

It's OK now. maybe I mistype something.

Thank you all.

Bobocat

 

[Don Guillett]

glad to help