Dynamic Named Ranges with Blank Cells

Hi All,

I will use an example to demonstrate my problem. I have a spreadsheet with the number 1 contained in each cell of the range A1:A10.

i.e.

A1
1
1
1
1
1
1
1
1
1
1

I then use the formula =OFFSET(temp!$A$1,0,0,COUNTA(temp!$A:$A),1) to name the dynamic range (Temp_Range).

This formula only seems to work as long as there are no blank rows in the list.

i.e. If I use the formula Counta(Temp_Range) the result is 10. If I delete cell A10 the result is 9. If I delete cell A2 though the result is 8 and should be 9.

Essentially, does anybody have a formula for naming a dynamic range which works with lists with blank cells in it.

Thanks

Andy

To expand the range to the last text entry...

=OFFSET(temp!$A$1,0,0,MATCH("*",temp!$A:$A,-1),1)

To expand the range to the last numeric entry...

=OFFSET(temp!$A$1,0,0,MATCH(9.99999999999999E+307,temp!$A:$A),1)

Hope this helps!

Originally Posted by andibevan

Hi All,

I will use an example to demonstrate my problem. I have a spreadsheet with the number 1 contained in each cell of the range A1:A10.

i.e.

A1
1
1
1
1
1
1
1
1
1
1

I then use the formula =OFFSET(temp!$A$1,0,0,COUNTA(temp!$A:$A),1) to name the dynamic range (Temp_Range).

This formula only seems to work as long as there are no blank rows in the list.

i.e. If I use the formula Counta(Temp_Range) the result is 10. If I delete cell A10 the result is 9. If I delete cell A2 though the result is 8 and should be 9.

Essentially, does anybody have a formula for naming a dynamic range which works with lists with blank cells in it.

Thanks

Andy

Thanks for your help Dominic. Unfortunately that doesn't seem to work when you use the dynamicly named range as part of an array formula. Any ideas?

Thanks

Andi

Originally Posted by andibevan

Thanks for your help Dominic. Unfortunately that doesn't seem to work when you use the dynamicly named range as part of an array formula. Any ideas?

Thanks

Andi

Can you provide more details? Also, which formula are you using?

...this in the named range definition:

=OFFSET(temp!$A$1,0,0,MATCH(9, temp!$A:$A),1)

It works for me.

No Problem,

I have used your text forumla to name column A (Name_Temp) and your numerical formula to name column B (Version_Temp).

I then use the following array formula (entered using ctrl,shift and enter) :-

{=MAX((Name_Temp="Test Plan.doc")*(Version_Temp))}

but I then get a #value error.

Got any ideas?

Andi

Just in case I haven't been clear enough:-

Column A = File Names (dynamic range using text formula)
Column B = Document Versions (dynamic range using numbers formula)

I then wish to use this array formula:-

{=MAX((Name_Temp="test doc")*(Version_Temp))}

but I get a Value# (there is definately an item for test doc)

But when I do normal statistical calculations I get a result e.g.

Rows(Name_Temp)
Count(Version_Temp)

So basic calc seem to work but not more complex array formula.

I think I have worked this out. It seems to be to do with there being header rows for the data. How would I adjust the offset to allow for a header row? (or 4 header rows)?

The reason you're getting a #VALUE! error is that the text formula for the dynamic range gives you one size array and the numerical formula for the other dynamic range gives you another size array. The arrays need to be the same size. Try the following...

For your dynamic range:

=OFFSET(temp!$A$2,0,0,MATCH("*",temp!$A:$A,-1)-1,1)

For your formula:

=MAX((Name_Temp="Test Plan.doc")*OFFSET(Name_Temp,0,1))

OR

=MAX(IF(Name_Temp="Test Plan.doc",OFFSET(Name_Temp,0,1)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Originally Posted by andibevan

No Problem,

I have used your text forumla to name column A (Name_Temp) and your numerical formula to name column B (Version_Temp).

I then use the following array formula (entered using ctrl,shift and enter) :-

{=MAX((Name_Temp="Test Plan.doc")*(Version_Temp))}

but I then get a #value error.

Got any ideas?

Andi

Well I seem to have sorted this one out. It seemed to be a number of things in the end and your help (Dominic) has been very useful.

Many thanks

ATB

Andi