Question about dynamic named range

B

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
 
A

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
 
D

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

Bobocat

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