Character Limit for Drop Down List coding in Excel2010??

[kreiter]

I've been working on a spreadsheet for custom quoting jobs for my business. It has multiple dropdown lists feeding from different worksheets where one of the lists is dependent on the other. The first drop down is straightforward and is a choice of every material we have available. The second one then lists the different thicknesses of said material and I have about 40-50 material choices with different thicknesses and all having their own worksheet. Instead of the INDIRECT command which I have found to not be suitable for my application, I'm using a lot of "If" statements within the data validation field, for example: "IF(B4=Lists!A13, list1, IF(B4=Lists!A14, list2, etc." and I have a lot of IF statements I need to put in for each material.

My issue is that I have seem to have hit a character limit in the validation field and can't think of another way I could possibly do this. Any help on different ways to do this? And sorry if I didn't explain it well, it's a weird spreadsheet to orate. Thank you for the help!

 

[RoryA]

I guess my first question would be why isn't INDIRECT suitable? If it's because you have dynamic ranges, you can work around that with another defined name that simply replaces INDIRECT with EVALUATE.

 

[kreiter]

Oh, Then I guess I may not completely understand how the INDIRECT command works as well as I thought. I thought it was whatever string or whatnot is selected inthe first list, it will search through the worksheet and return the list that is named that same thing. My problem is that The names of some of the materials aren't suitable group names, i.e. N-5408, RN-8011, etc.

 

[RoryA]

In that case I'd probably use INDIRECT in conjunction with a lookup table to return the range name for a given code.

 

[kreiter]

Thanks for the help! But how would I do that? Never done either one of those...

 

[kreiter]

I tried using the INDIRECT function but I still can't think of another way around long IF statements. Most the material selections are either two words or things like RN8011 where it ends up being a cell reference. I can't find a way to select something in the drop down list and just have that reference a group of cells because the names for the corresponding cells can't have spaces of be references to other cell names.