Dynamic Drop Down lists

[wolfschenck]

Hello

I am trying to create a formula to insert into a cell that will choose a dropdown list dependent on the value entered in a different cell.  See the attached file KSS Worksheet.  On the KSS Prep sheet I have shape and description.  On the data sheet I have a list of shapes in column A, this works fine, but then I wish the description cell in the KSS Prep sheet to get a value from the rest of the columns in the Data sheet.  The length of the columns can be quite extensive up to 300 values in each column.  Each column will be varying in length.

[#2 · Accepted solution]

@wolfschenck 

See the attached version.

[#3]

Wow great thank you
I will put it to work and show you the results.

[#4]

Hans Vogelaar
The method you gave me works great! Thank you.
One more question....if the value I must enter into a cell is NOT on the dropdown list, excel gives me an error stating that it is not a value in the list and will not allow the entry. How do I not only have the list to choose from but can also enter an entirely different value? Do I have to delete the data validation from that cell and then enter a value? OR can I add a blank to the dropdown list and it will allow me to enter a different value? OR what method else is there?
Thanks Again

[#5]

@wolfschenck

Select the cell and click Data Validation on the Data tab of the ribbon.

Activate the Error Alert tab of the dialog.

If you would like to allow other values, but still get a warning, set the style to Warning or Information instead of Stop:

If you don't want a warning at all, clear the "Show error alert ..." check box.

[#6]

Thank you that works well.