Data Validation One Dependent

What are you trying to achieve? For dependent validation, you need named ranges which match the values of the 'parent' list.

So, for a validation list to change depending on which product you have chose, you need named ranges "Soft Sphere", "Soft Classic Sphere", "Soft Toric", then the data validation list formula should be, for example:

Formula:

=INDIRECT(SUBSTITUTE(D8," ",""))

I've added a couple of examples to your workbook (see attached) - changed cells highlighted yellow, and look at the extra named ranges I have added. You haven't stated any of your actual requirements, so the business logic won't make sense... but you should be able to see how the named ranges need to work, and the data validation references.