This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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," ","")))))))))))))))))))))))))))
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
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.
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!
OK - here is a partially corrected file:
https://1drv.ms/x/s!AsKdy7Nfg\_FbgWZvkGg\_2naD\_\_R0
What you need to do is change your named ranges to correspond to the text that is chosen in either cell C2 or C3. And you need to change your text so that it is descriptive of the range. Also, I have included event code to clear out the cells when C2 changes, and to add "Leave blank" into C3 when it is not needed.
For example - you had the named range OTHNS as the "Other" choice for Natural Sciences. I changed the text in the Natural Sciences list from "Other" to "Other Natural Sciences" and I changed OTHNS in the named range list to OtherNaturalSciences - the displayed text in C3 with the spaces removed. That is the only one that I fixed - the rest is up to you.
For the DV in C4, I used
=INDIRECT(IF(C3="Leave Blank",SUBSTITUTE(C2," ",""),SUBSTITUTE(C3," ","")))
So - any entry in C3 (or C2 for those that don't use C3) needs to be a named range when the spaces are removed from the text.
If you need further clarification, post back.
https://1drv.ms/x/s!AhyDdnjegKkha81RFAywTSqQbXo
It worked! Kindly try it now, and thank you very much for your patience.
Awaiting your reply.
It's not allowing me to save the file. I don't use sharepoint - I use onedrive.live.com and have never had this issue.
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?