Multiple Dropdown list does not populate data as desired.

I don't see an error popping up.

You can select all the data, and create named ranges, then you can use the named ranges to fill in the second column.

Here is a clip on your specific worksheet.

HN1961 clip

There is no sound, just a visual.

A couple of questions might pop up.
1- hit F5 to get the special cells window
2- hit F3 to get the paste name window for the data validation list

The formula for the second column dataval is

Formula:

Please Login or Register  to view this content.

The reason for the substitute is that excel will not allow two word named ranges. so it will make the space a "_".

If you look at the named ranges, you will see a "_" where the spaces should be in the named ranges.