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

It worked for me in the version that I uploaded last - it is possible that one event is triggering another, getting into an infinite loop.  So, try this version:

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

It allows me to make any and all changes - so here's hoping...

Change the event code for sheet GE Courses Description to the code below, but also you will need to change the lookup cells - for example, C5 - from

=IF(ISTEXT($C$4),VLOOKUP($C$4,'GE Database'!M4:S444,2,FALSE),"")

to

=IFERROR(IF(ISTEXT($C$4),VLOOKUP($C$4,'GE Database'!M4:S444,2,FALSE),""),"")

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").Value = "--Select a course--"

    Else

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

        Range("C3").Value = "--Select a field--"

        Range("C4").Value = "--Select a course--"

    End If

    Application.EnableEvents = True

End Sub

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.

I don't think you understood my suggestion.

"And I don't know how to shorten that function as I have different names used for different ranges."

You need make a table of the unique names that you use for the different ranges, based on the unique combinations of the values in C2 and C3.  The "Others" from English and from Social Sciences must have different names, and the table that you create will ensure that those ranges are correctly found using the VLOOKUP, allowing INDIRECT to properly function.

If you can share a version of you file, I can show you what needs to be done with that table.

Thank you Bernie for your kind help. 

The thing is that my issue is far more complicated.

I have a database of about 1,000 cells, and they include university courses and titles. So we have a list of categories that include different types of fields, which include the relevant courses. The only problem I am facing is that a specific field is named more than once in my database, that's why I'd have to define different names like "_Oth1" whereas I should be naming it "Others" for the indirect function to look it up.

I will explain my problem with corresponding pictures, as I would appreciate your efforts to help me solve this problem.

1. The user visits a sheet, and chooses an area he/she wants to find their courses in

2. They then after choose a field for the corresponding area, before they are asked to choose the course, so we are filtering our choices before giving the user the chance to choose their course.

The long function that I had posted on this page refers to the third row, which is the "Course" that the user has to choose after picking up a choice from the two previous options. And I don't know how to shorten that function as I have different names used for different ranges.

As you can see below, my database has many duplicate names, I am sharing this so that whoever can offer help would better understand what I mean.

This is the beginning of my database, and the areas field is the field option a user has to choose. The orange cells refer to the "Field" categories, which is the second option the user has to choose from (e.g. Humanities I/Social Science List I). 

And as you can see in the last two pictures, i have duplicates in the "Field" cells (the light greenish cells). 

This is why I had created the previous long IF function, but couldn't use it on the data validation due to the character limit. I know there is a way for VLOOKUP to do the job for me, but I can't seem to know how to solve it. 

Any help is very much appreciated!