Range name limit for data validation
 

Hello!
I'm running into an Excel limit with the number of range names I can enter
in my formula. Below is the formula entered into the source field of the
Data Validation box (3rd list). When I try to enter any additional range
names,
(i.e. vf30) I get an error. I have 3 lists, the second list is dependent on
the
selection from the first list and the third list depends on the selections
made
from the first and second lists.

Any help would be appreciated. Thank you.

=CHOOSE(MATCH
E7,producttype,0),vf1,vf2,vf3,vf4,vf5,vf6,vf7,vf8, vf9,vf10,vf11,vf12,vf13,vf14,vf15,vf16,vf17,vf18,v f19,vf20,vf21,vf22,vf23,vf24,vf25,vf26,vf27,vf28,v f29)

Paul K. wrote...
I'm running into an Excel limit with the number of range names I can
enter in my formula. Below is the formula entered into the source
field of the Data Validation box (3rd list). When I try to enter any
additional range names, (i.e. vf30) I get an error. I have 3 lists,
the second list is dependent on the selection from the first list
and the third list depends on the selections made from the first
and second lists.

Any help would be appreciated. Thank you.

You had a typo, missing left parenthesis after MATCH, in your formula,
so I suppose it should appear as

=CHOOSE(MATCH(E7,producttype,0),vf1,vf2,vf3,vf4,vf 5,vf6,vf7,vf8,vf9,
vf10,vf11,vf12,vf13,vf14,vf15,vf16,vf17,vf18,vf19,
vf20,vf21,vf22,vf23,vf24,vf25,vf26,vf27,vf28,vf29)

The problem is that Excel accomodates only 30 arguments to *ANY*
function. The formula above has just hit the 30 argument limit. You
can't add any more no matter how badly you may want to do so.

There may be work-arounds. If all these VF# named ranges are direct
references to ranges, that is, the name VF99 refers to =X!$Z$500 rather
than a dynamic range like =OFFSET(X!$A$1,COUNT(foo),5,1,1), then you
could use INDIRECT and rewrite your formula as

=INDIRECT("VF"&MATCH(E7,producttype,0))

Or INDEX(VF1:VF29,MATCH(E7,ProductType,0))

On 7 Feb 2005 11:01:28 -0800, "Harlan Grove" wrote:

Paul K. wrote...
I'm running into an Excel limit with the number of range names I can
enter in my formula. Below is the formula entered into the source
field of the Data Validation box (3rd list). When I try to enter any
additional range names, (i.e. vf30) I get an error. I have 3 lists,
the second list is dependent on the selection from the first list
and the third list depends on the selections made from the first
and second lists.

Any help would be appreciated. Thank you.

You had a typo, missing left parenthesis after MATCH, in your formula,
so I suppose it should appear as

=CHOOSE(MATCH(E7,producttype,0),vf1,vf2,vf3,vf4,v f5,vf6,vf7,vf8,vf9,
vf10,vf11,vf12,vf13,vf14,vf15,vf16,vf17,vf18,vf19 ,
vf20,vf21,vf22,vf23,vf24,vf25,vf26,vf27,vf28,vf29 )

The problem is that Excel accomodates only 30 arguments to *ANY*
function. The formula above has just hit the 30 argument limit. You
can't add any more no matter how badly you may want to do so.

There may be work-arounds. If all these VF# named ranges are direct
references to ranges, that is, the name VF99 refers to =X!$Z$500 rather
than a dynamic range like =OFFSET(X!$A$1,COUNT(foo),5,1,1), then you
could use INDIRECT and rewrite your formula as

=INDIRECT("VF"&MATCH(E7,producttype,0))

"Myrna Larson" wrote...
Or INDEX(VF1:VF29,MATCH(E7,ProductType,0))
....

Indeed!

When did Microsoft expand Excel to 578 or more columns? My version only
extends to column IV.

Maybe the OP was goofing with range references in 'col VF', but I took him
at his word that these were named ranges.

Can somebody tell me how do I get to cell VF1 ?<g
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


"Myrna Larson" wrote in message
...
Or INDEX(VF1:VF29,MATCH(E7,ProductType,0))

On 7 Feb 2005 11:01:28 -0800, "Harlan Grove" wrote:

Paul K. wrote...
I'm running into an Excel limit with the number of range names I can
enter in my formula. Below is the formula entered into the source
field of the Data Validation box (3rd list). When I try to enter any
additional range names, (i.e. vf30) I get an error. I have 3 lists,
the second list is dependent on the selection from the first list
and the third list depends on the selections made from the first
and second lists.

Any help would be appreciated. Thank you.

You had a typo, missing left parenthesis after MATCH, in your formula,
so I suppose it should appear as

=CHOOSE(MATCH(E7,producttype,0),vf1,vf2,vf3,vf4,v f5,vf6,vf7,vf8,vf9,
vf10,vf11,vf12,vf13,vf14,vf15,vf16,vf17,vf18,vf19 ,
vf20,vf21,vf22,vf23,vf24,vf25,vf26,vf27,vf28,vf29 )

The problem is that Excel accomodates only 30 arguments to *ANY*
function. The formula above has just hit the 30 argument limit. You
can't add any more no matter how badly you may want to do so.

There may be work-arounds. If all these VF# named ranges are direct
references to ranges, that is, the name VF99 refers to =X!$Z$500 rather
than a dynamic range like =OFFSET(X!$A$1,COUNT(foo),5,1,1), then you
could use INDIRECT and rewrite your formula as

=INDIRECT("VF"&MATCH(E7,producttype,0))

"RagDyer" wrote...
Can somebody tell me how do I get to cell VF1 ?<g

Define VF1 as =Sheet1!X99. Use [F5].

But that's not cell VF1.
That's a single cell range, named VF1.

You saw my <g ... I didn't see yours.
That mean you were serious?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Harlan Grove" wrote in message
...
"RagDyer" wrote...
Can somebody tell me how do I get to cell VF1 ?<g

Define VF1 as =Sheet1!X99. Use [F5].

"RagDyer" wrote...
....
You saw my <g ... I didn't see yours.
That mean you were serious?
....

No, that means I don't use sign posts for the irony-challenged.

Harlan,

"Good Answer." Your solution worked. Thank you much! Paul

"Harlan Grove" wrote:

Paul K. wrote...
I'm running into an Excel limit with the number of range names I can
enter in my formula. Below is the formula entered into the source
field of the Data Validation box (3rd list). When I try to enter any
additional range names, (i.e. vf30) I get an error. I have 3 lists,
the second list is dependent on the selection from the first list
and the third list depends on the selections made from the first
and second lists.

Any help would be appreciated. Thank you.

You had a typo, missing left parenthesis after MATCH, in your formula,
so I suppose it should appear as

=CHOOSE(MATCH(E7,producttype,0),vf1,vf2,vf3,vf4,vf 5,vf6,vf7,vf8,vf9,
vf10,vf11,vf12,vf13,vf14,vf15,vf16,vf17,vf18,vf19,
vf20,vf21,vf22,vf23,vf24,vf25,vf26,vf27,vf28,vf29)

The problem is that Excel accomodates only 30 arguments to *ANY*
function. The formula above has just hit the 30 argument limit. You
can't add any more no matter how badly you may want to do so.

There may be work-arounds. If all these VF# named ranges are direct
references to ranges, that is, the name VF99 refers to =X!$Z$500 rather
than a dynamic range like =OFFSET(X!$A$1,COUNT(foo),5,1,1), then you
could use INDIRECT and rewrite your formula as

=INDIRECT("VF"&MATCH(E7,producttype,0))