How to bypass/workaround for 255 character formula limit? need help please.

That makes it a clearer. But I still think you make it to complicated by using very long named ranges and you will keep running into limits, also limits to the human brain to understand long formulas...

So I simplified the required named ranges and tables to use. Use 3 tables and in essence 3 named ranges to handle all
see below what I created in the attachement..

	A	B
11	Tabels	Description of content
12	Tbl_Competencylvl	Simple table with 5 competency levels and setup of dependant dropdowns
13	Tbl_DevelopmentTarget	All development targets followed by a general description fitting the competency level
14	Tbl_LearnObj	Holds all learning objectives per Dev Target code
15
16	Named Ranges	Description of content
17	DevCode	Finds the code of The Development target in column A
18	LearnObjective	Finds the available learning objectives for the chosen Target and Comp. Level
19
20	Competency_level	Standard dropdown for B19
21	None	Dependant dropdown for C19 based on choice B19
22	Advanced	Dependant dropdown for C19 based on choice B19
23	Basic	Dependant dropdown for C19 based on choice B19
24	Intermediate	Dependant dropdown for C19 based on choice B19

The Named Range DevCode holds the LEFT / FIND function I gave earlier, just a convienience thing, to use in other named range instead of left/find combi.

Also I created some dependant dropdown lists for the Target Compentency level (col C)
my reasoning is if you start at Basic the target level should be higher.. so for Basic name range it starts at intermediate.
I might be off and it might be a Basic-Basic development is possible, then simply add that to the named range list.
Or also use named range "Competency_level" for col C. But then there is more risk of error if someone select a lower Target then current level

The most important named range is the LearnObjective I will explain in detail. this is the formula

Formula:

Please Login or Register  to view this content.

reference____Sheet2! $Q$1___________________________________is the topleft cell of tabel Tbl_Learnobjective
row__________MATCH(DevCode;Tbl_LearnObj[ Code];0)___________finds the first row with the code of the chosen Development target
column_______MATCH(ROC!$C$19;Tbl_LearnObj[ #Headers];0)-1___finds the correct column with the appropiate Competency level
heigth_______COUNTIF(Tbl_LearnObj[ Code];DevCode)___________is sort of a helper column it simply counts the amount of codes in the table Learn objectives.
width________1____________________________________________Keeps the dropdown list limited to 1 column

In the Table Tbl_LearnObj you can put as much objectives as you want and a variable number per DevelopmentTarget or target level, simply make sure that each (extra) line you put in holds the code of the Dev Target you want to list out.
I kept white lines between each DevCode but in the final table you can leave those out, just put them there to make it easier to understand what I did.

With these 3 basic tables you have maximum flexibility while each change will not cause you to change named ranges.
If you have new development target you just add it to the Tbl_DevelopmentTarget and fill the columns with appropiate texts. Also fill the tbl_LearnObj with the objectives per competency level
Even if at some point the competency requirements change you can simply add another row for that DevCode or remove one if it becomes obsolete.


please look at the attachement to see if this would make a (more) usable structure for you problem.
In this model also the 255 character limit is solved as per cell you can go to wel over 32000 characters