Creating validation drop boxes for a three column list

Janenebg

New Member
Joined
Jul 20, 2007
Messages
2
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

WinteE

Well-known Member
Joined
Apr 8, 2007
Messages
605
Hi Janene,

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

Erik
 

Janenebg

New Member
Joined
Jul 20, 2007
Messages
2
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

Well-known Member
Joined
Apr 8, 2007
Messages
605
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
 

Forum statistics

Threads
1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top