Thread: Macro for Data Validation Lists?

Greetings,

I've been using Excel for years and just recently found this forum extremely helpful! Thank you for reading.

I've attached a portion of my workbook to make this a little more clear. The List in E5 contains all the National Forests in Regions 2, 3, and 4. E6 will have all the Ranger Districts of each of those Forests. I'm trying to code the E6 List to be effected by E5. For example, if the user selects the Apache-Sitgreaves NF in E5, I want them to only be able to select from the five Ranger Districts on that Forest (Alpine, Black Mesa, Clifton...)

Here's my original thought on what I can write in the Source field under Data Validation:

=IF(E5=K5,K49:K53,(IF(E5=K6,K57:K60,(IF(E5=K6,

...and so on. This could take millennia considering there are a 41 Forests that have to go into that formula. Is it possible to code VBA or record a macro to make this easier? I've had very minimal training on VBA and macros. So, if it is possible, can anyone recommend a good source for learning VBA (book, website, etc.).

Cheers

Data Validation Excel Forum.xlsx

Kanienkeha4,

Welcome to the forum!
No need for a macro on this, you just have your data laid out so that excel won't work well with it. Attached is a modified version of your workbook. I laid out the forests and their districts in a more excel-friendly format (the columns can be hidden if preferred). The data validation formula in cell B5 is:

=IF($B$4="",$F$3,OFFSET($F$2,,MATCH($B$4,$G$1:$AU$1,0),MAX(COUNTA(OFFSET($F:$F,,MATCH($B$4,$G$1:$AU$1,0)))-1,1)))

Doing this, I noticed that the following items in the forest list did not have a district list (the ones ending with NG):
Black Kettle-McClellan NG
Buffalo Gap-Fort Pierre-Oglala NG
Cimarron-Comanche NG
Curlew NG
Kiowa-Rita Blanca NG
Pawnee NG

Is that intentional?

Kanienkeha4,

You're very welcome
And thank you for the NG explanation, that makes more sense now.