Data Validation Source Limit

[j.stitt1]

Hi

In data validation is ther a limit to the length of the formula in the source?

Excel won't let me put any more in, and i still have Eleven more to add

(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($K8,"Rect. Tube","Recttube"),"Sq. Tube","Sqtube"),"Rd. Tube","Rdtube"),"Square Bar","SqBar"),"Pipe (Nom)","NomPipe"),"Sch. Pipe Joint","PipeJoint"))

I am substituting because i have two drop down list use the Reference same range name but have different list.

Is there a better way to do the same thing?

Jeffrey

 

[AlphaFrog]

There is a 255 character limit.

I don't quite follow why you are doing it this way so I can't offer an alternative.

If you want to reference two different lists based on K8, Maybe name the two lists the same with a number 1 or 2 (MyList1 and MyList2). Then if K8 is MyList, just add the appropreate number e.g. K8 & 1

 

[Peter_SSs]

I'm also not quite sure where you are headed with this but so far, you appear to be basically removing spaces, periods and parentheses. Could you use something like this instead?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($K8," ",""),"(",""),")",""),".","")

It would mean that you would have to change some of your named ranges. They would have to be the same as the $K8 value but with the spaces, periods and parentheses removed. For example, instead of "NomPipe" that named range would have to be "PipeNom"

 

[j.stitt1]

Peter

I tested the formula in a cell and it works great.

I tried to cut and paste in my drop down list with indirect at the beginning
and it gives me a error.

What do you think?

jeffrey

 

[j.stitt1]

Peter
I got It.