Yet another excel question on table data

[Cyclonesrule91]

I have Excel 2013 and have a question on how to get info from a table.

I have a table that has all of Iowa counties and then has different rates for each county. Prior to this current year I used VLookup function to find the rates and it was pretty painless.

This year fro whatever reason, they now have different rates within each county based on what township and range you are in. So instead of just choosing the county and getting the rate, I now have to select the county, the township, and the range in order to get the rate.

So I have two questions for this new example.

1. Is there a way I can create drop down boxes for each that is specific to the selections I choose? For example, the first box would be all the counties in Iowa and I can get that. Wondering how to make it so once I pick a county, when I go to the Township dropdown that only the townships specific to the county I selected would appear? And the same applies to the range choices

2. What function would I use that once I select the county, township and range, would go and find the rate specific to those selections?

Even if I could get a answer telling me to function to use and I could google it to learn how to use it would be greatly appreciated. Prefer it to not be associated with Macro VBA programming because I am not to educated on that yet.

Thanks in advance.

 

[Bader]

So playing around for a bit I was able to drive drop downs based on each other by doing something like this:
Drop down A is in cell A9.
The two columns of data for drop down B are in columns C and D

In the select line from drop down B the logic looks like:
=IF($A$9="a",$C$1:$C$7,$D$1:$D$7)

I imagine you can clean this up quite a bit using named lists, but depending on how much data you have to work with in each drop down it might get out of hand pretty quick

 

[CornFedIABoy]

Do the ranges perfectly nest with the townships? I assume the townships all perfectly nest in the counties?

 

[Bader]

If you need more than two options (I assume you will) for selecting from you'll end up nesting IF statements

IF($A$9="a",NameOfAData, IF($A$9="b",NameOfBData, NameOfCData) )

 

[bpmdu]

Alternatively, you can name your fields (i.e. data in column A, column B, column C, etc) and then use the indirect function to create dependant drop down menus.

 

[Bader]

Alternatively, you can name your fields (i.e. data in column A, column B, column C, etc) and then use the indirect function to create dependant drop down menus.

This makes more sense, you could do something like
For the "county township" drop down
=INDIRECT($A$9 & "Township")

Then name your township lists as PolkTownship DallasTownship etc

 

[Cyclonesrule91]

Do the ranges perfectly nest with the townships? I assume the townships all perfectly nest in the counties?

If I am correct at what you are asking on if 77N Township and 33W Range is specific to only Adair county then no. Township goes horizontal and Range goes vertical so they can be in multiple counties. Hope that makes sense.

 

[Cyclonesrule91]

So playing around for a bit I was able to drive drop downs based on each other by doing something like this:
Drop down A is in cell A9.
The two columns of data for drop down B are in columns C and D

In the select line from drop down B the logic looks like:
=IF($A$9="a",$C$1:$C$7,$D$1:$D$7)

I imagine you can clean this up quite a bit using named lists, but depending on how much data you have to work with in each drop down it might get out of hand pretty quick

There are 100 counties in Iowa, most with 4 townships and 4 ranges so 16 different combinations in each county.

I thought about using a unique list to come up with the 100 counties and naming the list. Then for each county making a Township named list and a Range named list and then use dependent data validation, but that could take forever and make for some very large formulas.

 

[Cyclonesrule91]

This makes more sense, you could do something like
For the "county township" drop down
=INDIRECT($A$9 & "Township")

Then name your township lists as PolkTownship DallasTownship etc

Might take time to set up all the lists, but this might do the trick.

 

[Cyclonesrule91]

Ok I made a bunch of lists so I could make my drop downs populate correctly. So no if I want to find a rate say in row "D" based on "County" (A), "Township" (B), and "Range" (C), what is the best function to use for that?