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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2017-01-24T22:38:42+00:00

    https://1drv.ms/x/s!AsKdy7Nfg\_Fbgi\_0MeTwatZSy8Oi

    On "Summary", we cannot do what you want in the same way - only entire rows or columns can be hidden.

    If D15="" (as in empty and has no text in it) --> D12 to H22 "Hide"

    Your formulas that return "" effectively hide those cells. We could apply Conditional Formatting to change them to white text on a white background - that is easy to do, but not really needed.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2017-01-24T22:12:33+00:00

    https://1drv.ms/x/s!AhyDdnjegKkhbE8anGeR2Gs4m-4

    The comments are highlighted in yellow and they can be found in the first sheet called "GI Calculator" and the second sheet called "Summary".

    Again, THANK YOU!

    Please notify me if anything went wrong while downloading or while checking the workbook

    Was this answer helpful?

    0 comments
  3. 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
  4. Anonymous
    2017-01-24T21:12:08+00:00

    Although I am very embarrassed from the questions I had already asked, but could you kindly do the "Hide" VBA code for 3 easy criterias if I share with you a link to another sheet? Please excuse my ongoing questions

    Was this answer helpful?

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