Macro to create a dynamic drop down list

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Nice sample: all the numbers in the column Department are unique>
Joking?

HI, I changed the code I gave you guys a little, but now it is showing the next line as an error.

Please Login or Register  to view this content.

Here is the code and the worksheet.
Some things to know:
There is a new sheet called List, this will be the sheet with the data source for the drop down. This sheet can be hidden or very hidden. If you want very hidden then in the project explorer from the view menu choose properties and left click to select the sheet and in the properties window next to Visible choose very hidden.

The macro code can be moved to the activate event for the sheet with the drop down so that it will run every time that sheet is activated or you can leave it where it is and just run it as needed. There is a dynamic named range on the list sheet which is referenced in the drop down as the source for data.

Please Login or Register  to view this content.