Data Validation - Is there a way to surpass the 255 characters limit?

Anonymous

I had created the below formula, which is used for a drop-down list I am preparing. But when I came to paste it in the data validation box, it turned out that I can't insert more than 255 characters, while mine is 776 characters. Any help?

=IF(C2="Humanities List I",IF(C3="English",ENGL1,IF(C3="Others",_OTH1,IF(C2="Humanities List II",IF(C3="American and Media Studies",AMST2,IF(C3="Arabic and Near Eastern Languages",ARAB2,IF(C3="Architecture",ARCH2,IF(C3="Archeology",AROL2,IF(C3="English",ENGL2,IF(C3="Fine Arts and Art History",FAAH2,IF(C3="History",HIST2,IF(C3="Philosophy",PHIL2,IF(C3="Others",_OTH2,IF(C2="Natural Sciences",IF(C3="Others",OTHNS,IF(C2="Quantitative Thoughts",IF(C3="Others",OTHQT,IF(C2="Social Sciences List I",IF(C3="Others",OTHSS1,IF(C2="Social Sciences List II",IF(C3="Economics",ECON2,IF(C3="Education",EDUC2,IF(C3="Political Studies and Public Administration",PSPA2,IF(C3="Sociology and Media Studies",_SMS2,IF(C3="Others",OTHSS2,INDIRECT(SUBSTITUTE(C3," ","")))))))))))))))))))))))))))

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

45 answers

Anonymous

2017-01-24T21:17:38+00:00

Sure - now that we have the technique worked out!
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2017-01-24T20:53:49+00:00

You are very welcome - I hope that you have all your issues worked out.

You can always post on this thread, if your issue applies to this specific topic. I can't remember everything, but sometimes it comes back quickly enough, and I should notice that you posted a new message.

Thanks for the invite - I'm not much of a traveler, so this forum is where we will meet again.
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2017-01-24T20:47:18+00:00

Thank you very much again sir I appreciate it a lot. I hope you don't mind if I had any further questions in the future in case I posted them here.

If you ever make a visit to Lebanon or think of visiting, please do notify me on my email ******@live.com so we can meet!
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2017-01-24T19:14:03+00:00

To leave the list as Others for display, you could use this for the Data Validation source

=INDIRECT(IF(C3="Leave Blank",SUBSTITUTE(C2," ",""),IF(C3="Others",SUBSTITUTE(C3&C2," ",""),SUBSTITUTE(C3," ",""))))

But - all the Others ranges need to be named as Others + what is in C2 (but without spaces), like

OthersNaturalSciences

OthersSocialSciencesListII

And, to hide the row, this modification to my code should work:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$C$2" Then Exit Sub

Application.EnableEvents = False

If Target.Value = "Arabic Communication Skills" Or Target.Value = "English Communication Skills" Or Target.Value = "Writing in Discipline" Then

Range("C3").Value = "Leave blank"

Range("C3").EntireRow.Hidden = True

Range("C4").ClearContents

Else

Range("C3").EntireRow.Hidden = False

Range("C3:C4").ClearContents

End If

Application.EnableEvents = True

End Sub
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2017-01-24T18:22:00+00:00

That's what I get for trying to upload an update and share at the same time... sorry about that.

https://1drv.ms/x/s!AsKdy7Nfg\_Fbgi2DPEfYxHLAdRG1
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments