Is there a maximun number of cells allowed in a name range?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

peingle - 04 Jul 2009 03:03 GMT

It appears to be the case, but I don't know what the maximun is, or if my
error is for another reason.

Reply to this Message

Sheeloo - 04 Jul 2009 05:12 GMT

I don't think there is any limit...
In Excel 2003 I defined ColsBIV as
=Sheet1!$B:$IV
and then used
=SUM(ColsBIV) in A1
result was (with 1 as the value in all cells in the range B:IV
851968

which shows that name ColsBIV refers to 851968 cells

> It appears to be the case, but I don't know what the maximun is, or if my
> error is for another reason.

Reply to this Message

Sheeloo - 04 Jul 2009 05:18 GMT

Correction:

The result of the formula will give you
16711680
which is the sum of cells in 255 cols X 65,536 rows, each having a value of 1

> I don't think there is any limit...
> In Excel 2003 I defined ColsBIV as
[quoted text clipped - 8 lines]
> > It appears to be the case, but I don't know what the maximun is, or if my
> > error is for another reason.

Reply to this Message

FSt1 - 04 Jul 2009 05:16 GMT

hi
there is no maximum limit on the number of cells in a named range. what is
the error you are getting??? that would be the key.
so why did you not post that?!?!?!
we can only help if YOU give is all the facts.
regards
FSt1

> It appears to be the case, but I don't know what the maximun is, or if my
> error is for another reason.

Reply to this Message

Shane Devenshire - 04 Jul 2009 06:46 GMT

Hi,

I second FSt1's response - you should always post the error message AND you
should post any formulas that are failing. You also should tell us the
version of Excel you are using.

Possibly you are referencing an entire column, which in itself is not
illegal in a range name but, if you then use the range name in certain types
of formulas, in 2003 or earlier, you will get an error. Many 2003
formulas/functions did not allow full column references, those limits have
been removed.

Signature

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

> hi
> there is no maximum limit on the number of cells in a named range. what is
[quoted text clipped - 6 lines]
> > It appears to be the case, but I don't know what the maximun is, or if my
> > error is for another reason.

Reply to this Message

Dave Peterson - 04 Jul 2009 13:24 GMT

There is a maximum number of characters that you can type into that insert|Name
dialog in the refers to textbox (255 maybe???).

But there are ways around it.

> It appears to be the case, but I don't know what the maximun is, or if my
> error is for another reason.

Signature

Dave Peterson

Reply to this Message