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

Anonymous
2017-01-19T12:21:32+00:00

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," ","")))))))))))))))))))))))))))

Microsoft 365 and Office | Excel | For home | Windows

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

Sort by: Oldest
Most helpful Newest Oldest
  1. 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
  2. Anonymous
    2017-01-24T18:52:01+00:00

    Thank you very much my friend. You help is extremely appreciated as well as your patience. Also, thank you for the code that you had added, it is very helpful.

    If I may, I have another question that I can use your experience to help answer.

    Is there any way to use if I wanted the values in cell C3 to show the same way they were before? For instance, "Others" for "Natural Sciences" and "Others" for "Social Science List I", and not "Others Natural Science".

    You had mentioned the below formula previously, can I use it in any possible way while getting the same output for C4? Or I am only bounded by the names that you had asked me to define (e.g. OthersNaturalScience)?

    =INDIRECT(VLOOKUP(C2 & " " & C3, ListAddress, 2, False))

    Again, thank you very very much

    Was this answer helpful?

    0 comments
  3. Anonymous
    2017-01-24T19:03:00+00:00
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("C2").Value = "Humanities List I" Then
    Rows("3").EntireRow.Hidden = False
    Else
    Rows("3").EntireRow.Hidden = True
    End If
    End Sub

    An another question, also if I may. I found the above code online and I had "tried" editing it but not sure if it fully functions. I was trying to make the row 3 hidden in case the values of C2 were equal to either "Arabic Communication Skills" or "English Communication Skills". Almost similar to what you did. Is it possible, if both easy and simple, for you to send me the corresponding code so that I can use it on the workbook?

    Was this answer helpful?

    0 comments
  4. 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
  5. 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