Drop down list that sets up next drop down list in a form....

I am working on a form for our shop guys to use when setting up a job. Essentially I have a "Type" Drop down list where they have three options. The Type range is on sheet 5.

The next drop down list is where I am having problems. I have three sheets that have dynamic ranges with tool numbers in them. I want to be able to pick from the first drop down list and select say End Mill, after selecting End Mill, the next drop down list only displays end mills.

I have tried some If and then statements but not coming close.

Any help would be appreciated.

PS: I have seen examples but most of the time those are pulling from the same sheet. If it's not possible let me know.

The examples you have seen work regardless of sheet location of named ranges (assuming you've seen the same ones I have).

One caveat you may run into is if your Type list has End Mill in it, you cannot have a space in your End Mill list's defined name. I would go with naming it EndMill, then used the following formula for your data validation...

=INDIRECT(SUBSTITUTE(A1," ",""))

...where A1 would be changed to the cell address of the Type selection.

Originally Posted by Harribone

Create a list of End Mills that you want to show up. Select that range and name it 'End Mill'. Note this needs to be the same as it appears in the first drop down. Do this for each of the options.
Then usedata validation to create you second drop down and set the drop down list to =INDIRECT(A1) where A1 is the cell location of the first drop down.

Ha! We essentially said the same thing... but as a elaborated to, you can't have a space in the list name.

(Oh.. BTW, I started my reply before you posted, but got sidetracked and didn't check if anyone had posted before I submitted mine.)

Oh... another thing for Vike... when you define the named range, make sure the Scope is set for Workbook and not a specific sheet.

Thank you exactly what I wanted. Another quick question.........


If End Mills show up I have it where it will populate the the categories such as flute number and length. Is there an easy way to have it change out the categories if Drill is selected such as depth instead of say tool holder.

Would that be a vlookup? Where it looks to the original drop down list to populate the category type, and the second drop down list populate the actual data?

Originally Posted by FDibbins

jhren, thats a really good idea to remove the " " in the name. An alternative is to use _ in the range name (End_Mill). That way, if you need to break End_Mill apart for whatever reason, you can just search for "_", rather than trying to figure out where a " " would go in EndMill

I considered that, but then, in the indirect formula for data validation, you'd have replace the space with an underscore, which requires a slightly more complex formula.

Besides, we're talking about a range name. What might be the reason for needing to break it apart??? If we continue with OP's last question, the range header can still be End Mill for lookup purposes.

Originally Posted by Vikestand

Thank you exactly what I wanted. Another quick question.........


If End Mills show up I have it where it will populate the the categories such as flute number and length. Is there an easy way to have it change out the categories if Drill is selected such as depth instead of say tool holder.

Would that be a vlookup? Where it looks to the original drop down list to populate the category type, and the second drop down list populate the actual data?

Yes, but not necessarily VLOOKUP... and yes.

Originally Posted by FDibbins

I dont see how...
=INDIRECT(SUBSTITUTE(A1," ","_"))
is any more complex than...
=INDIRECT(SUBSTITUTE(A1," ",""))
But its really a matter of personal preferance

You're right!!! That's my inexperience showing

Originally Posted by jhren

Yes, but not necessarily VLOOKUP... and yes.

Not necessarily?

Thanks for the help so far.

"Not necessarily?" because we don't know how your tables are set up... may be vlookup, may be hlookup or just lookup, or perhaps an index/match formula.

If you had it working with just an endmill table , the formula will be similar but have to account for the variable category.

If the file size is too big to post, do a Save As, then delete stuff that is superflous and extensive to the objective (such as table data to a few examples rather than entries numbering in tens to hundreds)... i.e. reduce to simple example file. Or just create a simple example file using the same workbook structure (worksheets, table headers, ...) and only a few items in the tables. It's a lot easier to instruct on matters we don't have to speculate.

Another option is to upload to a site which permits larger file storage (e.g. Skydrive, Google Drive) and provide a link here. I'll even PM you with one of my email addresses if you want to send that way without file size reduction.

duplicate...

Thanks, I just sent you an email/attachment.