Create a Drop down list based on another drop down list value

**pdewet** · 07-26-2010, 04:48 PM

Hi

I have a worksheet with a legend sheet and various other sheets. I would like to in sheet 2 create a drop down list in column B related to sheet (Legend) column A1-C1. Then based on the selected value from dropdown list in column B would like to create another dropdown in column C from the legend sheet A2-A4 or B2-B3 OR C2-C5

Legend sheet
FORD MAZDA BMW
MIDGE STING X1
FOCUS BANTAM X3
SIERRA X5
X6

yOUR HELP WILL BE APPRECIATED

**contaminated** · 07-26-2010, 04:51 PM

Hi and welcome to the board
Visit here... may help

**pdewet** · 07-26-2010, 05:51 PM

Hi

Thank you is helped but i have some new challenges. I have attached some sample data fro my spreadsheet . This all to do with the legend. The all caps would be the Category and the rest would be the Type from the category

I have column B in my sheet 2 resolved to display the categories but now is to display the related type of the selected category in column C.

A
TELEVISION ZOOM LENS SYSTEMS
Zoom Lenses
Servo Units
Focus/Zoom Controls

B
CAMERA SUPPORT SYSTEMS
Fluid Heads
Pedestals
Tripods
Dollies
Pan Bars

C
CABLING AND INSTALLATION
Installation Materials
Installation Labour
Project Management Fees
Consulting Fees
Project Training Fees

D
INFORMATION TECHNOLOGY SYSTEMS
Personal Computers and Laptop Systems
Network Switches and Hubs
KVM Switching Systems
KVM Extender systems
VGA Displays

Your help is really appreciated.

Philip

**pdewet** · 07-26-2010, 06:18 PM

Hi

I have a worksheet with a legend sheet and various other sheets. I have created in sheet 2 a drop down list in column B related to sheet (Legend) column A1-D1. Now based on the selected value from dropdown list in column B i would like to create another dropdown in column C from the legend sheet A2-A4 or B2-B6 OR C2-C6 or D2-D6. Now to display the related type of the selected category in column C.

A
TELEVISION ZOOM LENS SYSTEMS
Zoom Lenses
Servo Units
Focus/Zoom Controls

B
CAMERA SUPPORT SYSTEMS
Fluid Heads
Pedestals
Tripods
Dollies
Pan Bars

C
CABLING AND INSTALLATION
Installation Materials
Installation Labour
Project Management Fees
Consulting Fees
Project Training Fees

D
INFORMATION TECHNOLOGY SYSTEMS
Personal Computers and Laptop Systems
Network Switches and Hubs
KVM Switching Systems
KVM Extender systems
VGA Displays

Your help is really appreciated.

Philip

**teylyn** · 07-26-2010, 06:54 PM

see attached.

for the type drop-down, use this formula for the data validation:

=INDIRECT(SUBSTITUTE(J2," ","_"))

It will replace the blanks with underscores and thus create the range name that has been set up for the respective Category

cheers