Linked dynamic named ranges - unexpected results.

[Ramses505]

I have recently come across some code to create a 'Dynamic' Named Range - this is really useful and I wish I had discovered it earlier
The code is like this

ActiveWorkbook.Names.Add Name:="Date", RefersTo:="=$B$3:INDEX($B:$B, COUNTA($B:$B)+3)"

This is operating on a column of dates where every row has a date (starting in Row 3), so works great. I have other Named Ranges which cover columns of non continous Row Data - there can (have to be) blanks. I have tried this code for another Named Range called 'Value' like this;

ActiveWorkbook.Names.Add Name:="Value", RefersTo:="=$C$3:INDEX($C:$C, COUNTA($B:$B)+3)"

This works but the Named Range 'Value' seems to have an extra Row in it !

If some DATA looks like this;
B C
1 1
1 1
1 1
1 1
1

The formula '=SUM(Date)' (in, say, E1) yields '4' and 'SUM(Value)' (in, say, F1) yields '5' - which is obviously wrong. If I add further rows to Col B and some extra data to Col C - the dynamic named range 'Date' expands as expected but the dynamic named range 'Value' always seems to contain 1 extra row.

By adjusting the formula for 'Value' to; =$C$3:INDEX($C:$C, COUNTA($B:$B)+2) (Note the +2 instead of +3) everything works as I would expect it to, which is good.

What am I not understanding about what is happening here. Why does the 'Value' formula need to be different ?

Thanks for any help.

 

[Sergius]

Hello!
Not very strong, but shouldn’t Value have COUNTA($С:$С), instead of what you have COUNTA($B:$B)

 

[Ramses505]

Hello!
Not very strong, but shouldn’t Value have COUNTA($С:$С), instead of what you have COUNTA($B:$B)

OK, maybe I wasn't quite clear. The point of using 'COUNTA($B:$B)' instead of 'C' is so that the second named range (the one with blanks in it) uses the same number of rows as the dynamic named range which does not have blanks. Does that make more sense ? Thanks