Dynamic Named ranges in formulae

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandy - 09 Feb 2008 17:31 GMT

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

Reply to this Message

Don Guillett - 09 Feb 2008 18:02 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I have a named range 'CountRecords' created by the following :-
>
[quoted text clipped - 10 lines]
> What am I doing wrong?
> Sandy

Reply to this Message

Sandy - 09 Feb 2008 18:22 GMT

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

> somewhere type =count(a:a) to see what you get.
>
[quoted text clipped - 12 lines]
>> What am I doing wrong?
>> Sandy

Reply to this Message

DJH - 09 Feb 2008 18:51 GMT

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

> Hi Don,
>
[quoted text clipped - 33 lines]
>>> What am I doing wrong?
>>> Sandy

Reply to this Message

Sandy - 10 Feb 2008 15:23 GMT

Thank you
Sandy

> Try
> =OFFSET(RecordofRounds!$A$53,0,0,COUNT(RecordofRounds!$53:$6700),1)
[quoted text clipped - 37 lines]
>>>> What am I doing wrong?
>>>> Sandy

Reply to this Message