Excel question again for the CF geniuses

[Cyclonesrule91]

I have posted quite a few excel questions on CF and everyone question has gotten me the replies I needed to do what I needed to do. Very appreciative of that but have another issue to fix.....

If I have a huge database that I need to form a list. One column are States, one column is all the counties in each state, one column is years going back to 1980 and yet another column gives me a number that represents each county for the years from 1980 to present. What I am looking to do is have drop down boxes where I can select the state, then the county and I will get a list of the number that represents that state and county representing years 1980 all the way up to 2012.

I am familiar with Hlookup and Vlookup but if I am searching by state and then county and then year to get me a number, not sure how that would work.

Can anybody help me out there?

Thanks in advance......:smile:

 

[IcSyU]

Sounds like you want a pivot table with report filter state & county, row label year, and value data.

Insert -> Pivot Table
Select your data source
Drag everything around to where you want it.

 

[ruxCYtable]

Pivot table will work but it may be more complex than you're looking for.

The easy way to get the drop downs that you want is to simply put your cursor in your top row of data then select DATA and (depending on what version of Excel you have) select FILTER or on some older version AUTOFILTER and it will create the drop down menu. Hope this helps.

 

[Cloner97]

You could also do a data validation list and use the Index function to do your individual state/country reports.

 

[Bigman38]

if you use excel all the time knowing how to do pivot tables will help you in the future so it might be worth taking the time to learn

 

[ruxCYtable]

if you use excel all the time knowing how to do pivot tables will help you in the future so it might be worth taking the time to learn

Pivot tables are the bomb

 

[HoopsTournament]

I don't think I would be able to do my job without pivot tables. Use them all the time.

 

[Cyclonesrule91]

I am familiar with pivot tables but it has been 3-4 yrs since I used them and don't know why I didn't consider that. If I have each States data on seperate sheets do I need to combine all the data into a single sheet for it to work right or can I pool the data together from multiple sheets to do that?

 

[Cyclonesrule91]

You could also do a data validation list and use the Index function to do your individual state/country reports.

I have used the index function to find the last entry on a list, but not sure what the data validation list is or what all can be done with the index function but will research this as well. Thanks

 

[State43]

 

[Cyclonesrule91]

OK, I combined all my data onto 1 sheet so I could make a pivot table and it seems to work except for one thing. I put State and County in the report filter, the year in the row label and the value in the value field. Selecting the state is alright, but is there a way the when I choose a state only the counties for that state will then appear in the County filter. Makes it pretty confusing when instead of having 100 counties in a state all the sudden you have to choose between thousands. Especially when multiple states have the same county name, you then have to select the right county name for the state you select.

I have been trying to use data validation lists as well but not sure how to use the index function to get my report like was mentioned above. Any help there would be appreciated as well.

Thanks again for all the help.

 

[IcSyU]

Ooooo ****. Hadn't thought of that.

Maybe concatenate the state name and county into 1 field?

 

[Pat]

Pivot tables are making this harder than it needs to be. Data > Filter > AutoFilter.

 

[Cyclonesrule91]

Ooooo ****. Hadn't thought of that.

Maybe concatenate the state name and county into 1 field?

BINGO!!! Did that and it works great. Thanks a ton....

Pivot tables are making this harder than it needs to be. Data > Filter > AutoFilter.

That is what I am using now. Problem is I need to get the numbers to another sheet so I can apply formulas to them to project trend numbers. So up until now I have been autofiltering down to what I need and then copy and pasting to another sheet so I can do the formula's. Wanting to automate so I can pick from drop down boxes and get the info and eliminate steps.