Forum Discussion
Help with data validation
Hi, there is a problem with data validation and the given data. When I have data that contains space between symbols, for example (BBA 4), when constructing a list in data validation and using a function indirect so it will be dependent lists, it does not work and show that it has an error in the data. How to solve this issue without changing space in data? Thanks
2 Replies
guapste Data Validation range names may not contain spaces. When you try to name a range "BBR 3" Excel won't allow you. When you create a name from a selection where the first cell contains "BBR 3", Excel automatically creates a name like "BBR_3", replacing the space with an underscore.
You have two options. Type BBR_3 in J25, or if you prefer not to show the underscore, use the formula below as the DV list source:
=INDIRECT(SUBSTITUTE($J$25," ","_"))
- If you are using Excel 365, perhaps this example helps: https://jkp-ads.com/Download.asp#DependentValidation
