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: Most helpful
Most helpful Newest Oldest
  1. 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
  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-24T17:45:38+00:00

    Dear Bernie, I believe you shared with me the wrong file. The link that you had sent is for an excel workbook called "Bonus Sheet". Mine was called "BBAGR Drive". If I can hassle you one last time with uploading the correct folder so I can better understand. Thank you again!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2017-01-24T11:26:53+00:00

    https://1drv.ms/x/s!AhyDdnjegKkha81RFAywTSqQbXo

    It worked! Kindly try it now, and thank you very much for your patience.

    Awaiting your reply.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2017-01-23T20:17:58+00:00

    https://mailaub-my.sharepoint.com/personal/kwa06\_mail\_aub\_edu/\_layouts/15/guestaccess.aspx?docid=17ce30cc41fa0461f8bc104e1e3f08839&authkey=AUiqswyNiyB4Ea99AXS2MrA&expiration=2017-02-20T08:30:30.000Z

    Although its is very weird, I press on the "Share" button, "Share with people", then I have 3 options "Invited people"/"Get a link"/"Shared with". And so I choose "Get link", and then "Edit link - no sign-in required" for the restrictions, then I copy the link and paste it here. Isn't that the right way of doing it?

    Was this answer helpful?

    0 comments