Creating validation drop boxes for a three column list

[Janenebg]

My list looks like this, There are three columns (Asset Category, Asset Type and Asset Sub Type). As you can see the first and second columns repeat to allow for the third column. This is a very long list, below is just a small part of it.

Asset Category, Asset Type, Asset Sub Type
Buildings and Improvements,Building Structure,Brick
Buildings and Improvements,Building Structure,Concrete
Buildings and Improvements,Building Structure,Steel
Buildings and Improvements,Building Structure,Timber
Buildings and Improvements,Roof,Ashphalt
Buildings and Improvements,Roof,Metal
Buildings and Improvements,Roof,Shingle
Buildings and Improvements,Roof,Shake
Buildings and Improvements,Mechanical,Boilers
Buildings and Improvements,Mechanical,Chillers
Buildings and Improvements,Mechanical,Air Handling Units
Buildings and Improvements,Mechanical,Pool water treatment and heating
Buildings and Improvements,Furniture and Fixtures,Floor Coverings
Buildings and Improvements,Furniture and Fixtures,Furniture
Buildings and Improvements,Furniture and Fixtures,Other
Land,Land associated with buildings,Municipal
Land,Land associated with buildings,Residential
Land,Parks
Land,Airport
Land,Cemetery
Land,Land under roads
Land,Right of Ways
Land,Vacant
Land Improvements,Landscaping
Land Improvements,Irrigation
Land Improvements,Fencing
Land Improvements,Lighting
Land Improvements,Water Control Devices
Land Improvements,Pathways & off road bike paths
Land Improvements,Parking Lots Pavement
Land Improvements,Parking Lots,Gravel
Land Improvements,Parking Lots,Dirt
Land Improvements,Parking Lots,Concrete

What I want is to be able to create a data entry sheet based on this list. I want to have the Asset Category, Asset Type and Asset Sub Type as drop boxes for the data entry. The asset category needs to be a distinct list, the asset type is based of the entry for the asset category and the asset sub type is based of the entry for the asset type. For example, if I choose "Land Improvements" in the Asset Category drop box, the Asset Type Drop box should contain "Landscaping, Irrigation, Fencing, lighting, etc." If I pick parking lots from the asset type drop box, then I should see "Pavement, Gravel, Dirt, Concrete". Some of the options don't have a value for the third category "asset sub type".

Can anyone help me!!! I also want to be able to add rows to this table and not have to keep adjusting my formulas.

Thanks Janene

 

[WinteE]

Hi Janene,

You can use Data, Validate for this. Take a look at my site for an example (Excel, Listbox).

Erik

 

[Janenebg]

Thanks but your lists are not setup the same way, I cannot go accross the columns this way as I have too much data. My list has only three columns but the values repeat.

I have looked at samples like this before and they don't work for me. Unless I am missing something....

Janene

 

[WinteE]

The long and the short of it is that you need to filter your list.

You nee a macro for this. The easiest way to make this work is by adding a calculated column to your list, including a multiple COUNTIF() formula.
With a macro all rows with value 1 can be selected now.

Is it a problem to add a calculated (hidden) column to your list ?

Erik

 

[Brian from Maui]

See,

http://www.mrexcel.com/board2/viewtopic.php?p=154#154