Dynamic Named ranges in formulae

[Sandy]

I have a named range 'CountRecords' created by the following :-

=OFFSET(RecordOfRounds!$A$1,52,0,COUNT(RecordOfRounds!$A:$A)-5,1)

which in the current sheet is the range A53:A80 - this is displayed
surrounded by a blue line when I type 'CountRecords' into the 'Name Box'

I then insert the following in cell A2 :-
=COUNT(CountRecords) and the display is '0'

If I enter COUNT(A53:A80) I get (correctly!) '28'.

What am I doing wrong?
Sandy

 

[Don Guillett]

somewhere type =count(a:a) to see what you get.

 

[Sandy]

Hi Don,

I get 33, which ties in with the -5 part of 'COUNT(RecordOfRounds!$A:$A)-5'.
The reason for the -5 is to deduct some cells with numbers I have in the top
area of my spreadsheet.

Thinking something had jammed up in excel I closed and then re-opened it,
which produced an error on opening of a circular reference. I presume that
is because the Name is being calculated using the entire column A and the
formula was using the same name and was in the name calculation range - if
you see what I mean.

Moving my =Count(ParBlock) to column B solved the problem. So.....is there a
way to use the =OFFSET()without encroaching on the cells or rows above row
53?

Interested in hearing your thoughts on all of this

Thanks
Sandy

 

[DJH]

Try
=OFFSET(RecordofRounds!$A$53,0,0,COUNT(RecordofRounds!$53:$6700),1)

 

[Sandy]

Thank you
Sandy

Try
=OFFSET(RecordofRounds!$A$53,0,0,COUNT(RecordofRounds!$53:$6700),1)