Forum

Notifications
Clear all

Data Validation with Dependent List

6 Posts
4 Users
0 Reactions
103 Views
(@yvonneqi2002)
Posts: 6
Active Member
Topic starter
 

Hi 

I have a problem in creating dependent lists with validation, and in my filter, I have the Data Validation entry with space, or with "&", or with "-" , I used =INDIRECT(SUBSTITUTE(J9," ","_"), it works for the entry with space, but it didn't work for "&" and "-". 

what I should do then? 

 

thanks, 

Yvonne Capture.JPG

 

 
Posted : 25/07/2020 6:33 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Yvonne,

Welcome to our forum! Please attach a sample Excel file so we can see the whole picture of your formula and how it relates to the worksheet. Anonymise it if required.

Mynda

 
Posted : 25/07/2020 7:57 pm
(@yvonneqi2002)
Posts: 6
Active Member
Topic starter
 

Hi Mynda, 

 

Nice to e-meet you! Here is the workbook I refer to.Confused

 
Posted : 27/07/2020 10:32 pm
Philip Treacy
(@philipt)
Posts: 1629
Member Admin
 

Hi Yvonne,

No attachment.  There's a video here showing how to attach files

https://www.myonlinetraininghub.com/excel-forum/forum-rules-and-guides/read-this-first

Regards

Phil

 
Posted : 27/07/2020 10:55 pm
(@yvonneqi2002)
Posts: 6
Active Member
Topic starter
 

HI 

Try it again. 

 
Posted : 28/07/2020 7:35 am
Anders Sehlstedt
(@sehlsan)
Posts: 968
Prominent Member
 

Hello,

In order to substitute several characters you need to nest the SUBSTITUTE function per character/word you want to change. You can read more about it in this blog post.

Example: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C9,"&","_"),"-","_")," ","_")

Br,
Anders

 
Posted : 28/07/2020 8:51 am
Share: