Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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) |
#2
|
|||
|
|||
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)) |
#3
|
|||
|
|||
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)) |
#4
|
|||
|
|||
"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. |
#5
|
|||
|
|||
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)) |
#6
|
|||
|
|||
"RagDyer" wrote...
Can somebody tell me how do I get to cell VF1 ?<g Define VF1 as =Sheet1!X99. Use [F5]. |
#7
|
|||
|
|||
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]. |
#8
|
|||
|
|||
"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. |
#9
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ClearContents method on a passed range | New Users to Excel | |||
Defined range difficulty | Excel Discussion (Misc queries) | |||
Data Validation Window? | Excel Discussion (Misc queries) | |||
limit worksheet view to specified cell range | Setting up and Configuration of Excel | |||
named range refers to: in a chart | Excel Discussion (Misc queries) |